Welcome to WebmasterWorld Guest from 54.167.153.63

Forum Moderators: open

Message Too Old, No Replies

How to optimize this query

   
9:31 pm on Mar 31, 2010 (gmt 0)

5+ Year Member



I am not very experienced with MySql and I would like to optimized the below queries. Please could you tell me what method would you suggest (join, union or other?). Is possible to make for all that only 1 query?

TABLES:
friends
who | whom | acceptedstatus

companies
companyid | companyname | url | companylogo

employees
id | companyid | userid

I have a page where I need to display to user all companies where user friends are employed. I have now the below code, but I believe it could be better optimized:


//$selfid is user id from session
$result=mysql_query("SELECT whom FROM friends WHERE who='$selfid'");
while($row=mysql_fetch_assoc($result){
$result2=mysql_query("SELECT companyid FROM employees WHERE userid='$row[whom]'");
while($row2=mysql_fetch_assoc($result2){
$result3=mysql_query("SELECT companyname, companylogo, url FROM companies WHERE companyid='$row2[companyid]'");
$row3=mysql_fetch_assoc($result3);
$companyname[]=$row3[companyname];
$compnylogo[]=$row3[companylogo];
$companyurl[]=$row3[url];
}
}
11:37 pm on Mar 31, 2010 (gmt 0)

WebmasterWorld Senior Member topr8 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



SELECT companyname,companylogo,url FROM friends,employees,companies
WHERE who=$selfid AND userid=whom AND companies.companyid=employees.companyid
1:23 am on Apr 1, 2010 (gmt 0)

10+ Year Member



or this:

SELECT companyname,companylogo,url
FROM companies
WHERE companyid = (SELECT companyid
FROM employees
WHERE userid = (SELECT whom
FROM friends
WHERE who ='$selfid')

10:18 am on Apr 1, 2010 (gmt 0)

5+ Year Member



Thank you! I found another option is also INNER JOIN. But now I am confused what is the best option.
a)
SELECT companyname,companylogo,url FROM friends,employees,companies
WHERE who=$selfid AND userid=whom AND companies.companyid=employees.companyid
b)
SELECT companyname,companylogo,url
FROM companies
WHERE companyid = (SELECT companyid
FROM employees
WHERE userid = (SELECT whom
FROM friends
WHERE who ='$selfid')
or c)
SELECT companyname, companylogo, url FROM companies
INNER JOIN employees ON employees.companyid = companies.companuid -- which have employess
INNER JOIN friends ON friends.whom = employees AND friends.who='$selfid'
9:24 am on Apr 7, 2010 (gmt 0)

5+ Year Member



That was very helpful. SELECT FROM WHERE is the standard for any operation. However could anybody brief me about how inner joins work?
3:55 pm on Apr 7, 2010 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



However could anybody brief me about how inner joins work?

Do you remember Venn diagrams from math? It's the same principle- you are comparing DB tables with JOINS (SQL) instead of sets with intersections (Venn diagrams).

The following 2 queries are identical:
SELECT Tablea.*, Tableb.*
FROM Tablea,Tableb
WHERE Tablea.idcolumn=Tableb.idcolumn

SELECT Tablea.*, Tableb.*
FROM Tablea INNER JOIN Tableb ON Tablea.idcolumn=Tableb.idcolumn
4:03 pm on Apr 7, 2010 (gmt 0)

WebmasterWorld Senior Member topr8 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



But now I am confused what is the best option.


generally (and it is only a generalisation) using subqueries will be slower

a subquery is something like:
WHERE userid = (SELECT whom FROM friends WHERE who ='$selfid')
4:16 pm on Apr 7, 2010 (gmt 0)

WebmasterWorld Senior Member demaestro is a WebmasterWorld Top Contributor of All Time 10+ Year Member



But now I am confused what is the best option.


I will also add that using a left join will return null results and inner join will not.
7:43 pm on Apr 7, 2010 (gmt 0)

10+ Year Member



I disagree with the statement that subqueries will generally be slower. Joins can be very expensive.

In the example given:

WHERE userid = (SELECT whom FROM friends WHERE who ='$selfid')

should be much faster than a join, as the subquery will only return one value for the outer query to check against. If the subquery returned a value for whom as 82, then it would be the same as saying: WHERE userid = 82
12:58 am on Apr 8, 2010 (gmt 0)

WebmasterWorld Senior Member topr8 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



>>If the subquery returned a value for whom as 82, then it would be the same as saying: WHERE userid = 82

no, not really. as in yes of course it would be like saying: WHERE userid = 82

however there is an expense in getting the value 82 in the first place, the table needs to be scanned, indexes looked up or whatever.

also it is my understanding (and i'm happy to be corrected) that MySQL does not process subqueries very efficiently - unlike sqlserver which is very efficient - after all as far as i know subqueries were only introduced from version MySQL 4.1

anyway i guess it depends on the data and the way the tables are indexed, for the future reference of anyone stumbling onto this thread running EXPLAIN for both variants will tell you which is more efficient!
9:09 pm on Apr 14, 2010 (gmt 0)

5+ Year Member



I have now another but similar problem. The situation is very similar but I need to get information from two tables. Is also for this any similar solution or do I need now to do actually a new query for each user?

table employees
id | userid | companyid | position

table users
userid | firstname | lastname | age | email | password ...

[php]
$employees=mysql_query("SELECT userid, position FROM employees WHERE companyid='$companyid' LIMIT $paginationLimit ");
while($rowemployees=mysql_fetch_assoc($employees){
$userid=$rowemployees[userid];
$position[]=$rowemployees[position];
$employeeDetails=mysql_query("SELECT * FROM users WHERE userid='$userid'");
$rowEmployeeDetails=mysql_fetch_assoc($employeeDetails)
$firstname[]=$rowEmployeeDetails[firstname];
$lastname[]=$rowEmployeeDetails[lastname];
$age[]=$rowEmployeeDetails[age];
$position[]=$rowEmployeeDetails[position];
}
[/php]

Thanks again!
7:04 am on Apr 15, 2010 (gmt 0)



Dear you just try w3c school for it
11:21 am on Apr 15, 2010 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



also it is my understanding (and i'm happy to be corrected) that MySQL does not process subqueries very efficiently - unlike sqlserver which is very efficient - after all as far as i know subqueries were only introduced from version MySQL 4.1


FWIW, I've been running subqueries with MySQL since it was in release candidate stages, prior to general availability in 2004 [mysql.com]. The only performance issues I have ever had with MySQL subqueries are the very same issues I've had with other RDBMS, including SQL server -- missing indexes. You plan and write the application, building indexes when you first create the tables. Then you end up writing a query that hits the table from a different angle than you originally planned. I've found that logging queries during development and testing initially, and then analyzing them during quality assurance testing of an application, to be extremely beneficial. Like you said, topr8, be certain to use the query analyzer provided by the RDBMS to find your performance bottlenecks.
11:25 am on Apr 15, 2010 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



The situation is very similar but I need to get information from two tables.


meee, read about the JOIN options that the others discussed so far.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month