homepage Welcome to WebmasterWorld Guest from 54.167.173.250
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
How to optimize this query
meee




msg:4107863
 9:31 pm on Mar 31, 2010 (gmt 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];
}
}

 

topr8




msg:4107903
 11:37 pm on Mar 31, 2010 (gmt 0)

SELECT companyname,companylogo,url FROM friends,employees,companies
WHERE who=$selfid AND userid=whom AND companies.companyid=employees.companyid

syber




msg:4107952
 1:23 am on Apr 1, 2010 (gmt 0)

or this:

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


meee




msg:4108162
 10:18 am on Apr 1, 2010 (gmt 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'

bizminder




msg:4111273
 9:24 am on Apr 7, 2010 (gmt 0)

That was very helpful. SELECT FROM WHERE is the standard for any operation. However could anybody brief me about how inner joins work?

LifeinAsia




msg:4111506
 3:55 pm on Apr 7, 2010 (gmt 0)

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

topr8




msg:4111511
 4:03 pm on Apr 7, 2010 (gmt 0)

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

Demaestro




msg:4111523
 4:16 pm on Apr 7, 2010 (gmt 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.

syber




msg:4111659
 7:43 pm on Apr 7, 2010 (gmt 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

topr8




msg:4111765
 12:58 am on Apr 8, 2010 (gmt 0)

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

meee




msg:4115987
 9:09 pm on Apr 14, 2010 (gmt 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!

propertyncr




msg:4116159
 7:04 am on Apr 15, 2010 (gmt 0)

Dear you just try w3c school for it

coopster




msg:4116269
 11:21 am on Apr 15, 2010 (gmt 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.

coopster




msg:4116270
 11:25 am on Apr 15, 2010 (gmt 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved