Welcome to WebmasterWorld Guest from 50.19.0.90

Forum Moderators: open

Message Too Old, No Replies

How to optimize this query

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

New User

5+ Year Member

joined:Dec 28, 2009
posts:12
votes: 0


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)

Senior Member

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

joined:Apr 19, 2002
posts:3169
votes: 8


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)

Preferred Member

10+ Year Member

joined:Nov 12, 2004
posts: 393
votes: 0


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)

New User

5+ Year Member

joined:Dec 28, 2009
posts:12
votes: 0


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)

Junior Member

5+ Year Member

joined:Mar 15, 2010
posts:57
votes: 0


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)

Moderator from US 

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

joined:Dec 10, 2005
posts:5550
votes: 24


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)

Senior Member

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

joined:Apr 19, 2002
posts:3169
votes: 8


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)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Dec 15, 2003
posts:2606
votes: 0


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)

Preferred Member

10+ Year Member

joined:Nov 12, 2004
posts: 393
votes: 0


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)

Senior Member

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

joined:Apr 19, 2002
posts:3169
votes: 8


>>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)

New User

5+ Year Member

joined:Dec 28, 2009
posts:12
votes: 0


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)

New User

joined:Apr 15, 2010
posts:2
votes: 0


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

Administrator

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

joined:July 31, 2003
posts:12533
votes: 0


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)

Administrator

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

joined:July 31, 2003
posts:12533
votes: 0


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.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members