Page 1 of 1

Which approach is faster to query

Posted: Sun Feb 17, 2008 4:35 pm
by micknc
I apologize if this should have been in the database section but I think it more of a php question.
I also apologize if my question is painfully obvious to the rest of you but here we go:

I need to get information out of three tables in a single database.
Is it better to write different queries like this:

Code: Select all

 
$query1 = "SELECT * FROM LINEITM WHERE SO_NO=$id";
 
$query2 = "SELECT LINEITM.SO_NO, CUSTSHP.NAME, CUSTSHP.ADDRESS, FROM LINEITM, CUSTSHP WHERE LINEITM.SHIP_ID = CUSTSHP.SHIP_ID AND LINEITM.SO_NO=$id";
 
$query2 = "SELECT LINEITM.SO_NO, SOHEAD.ORDER_DATE, SOHEAD.REQRD_DATE, SOHEAD.PO_NO FROM LINEITM, SOHEAD WHERE LINEITM.SO_NO = SOHEAD.SO_NO AND LINEITM.SO_NO=$id";
 
Or should I just do a join statement and put all the tables together. I have noticed that this page is very slow to load. Is it because I have written a bad script?
I am new and this seemed like a logical way to do it but since it is slow I figure it is bad. If it is bad can you elaborate on why. I am trying to learn instead of just getting the code I need today.

Thanks for your input.

Re: Which approach is faster to query

Posted: Sun Feb 17, 2008 4:36 pm
by Benjamin
A single query would be better.

Re: Which approach is faster to query

Posted: Sun Feb 17, 2008 4:46 pm
by micknc
Is there a reason it is so much slower? I had originally thought that it was becuase lineitm table has about 300K rows in it but I have read that mysql should handle that with no problem. I am already rewriting the script but I would like to understand more I guess so I don't duplicate this type of error in the future.

Thanks again.

Re: Which approach is faster to query

Posted: Sun Feb 17, 2008 4:52 pm
by Benjamin
There really shouldn't be a noticeable difference between having those 3 seperate and combining them. If one of them is slow you may need to add indexes. There's a lot of reasons why bundling queries is generally faster, some of which are debatable. On high traffic sites having all sorts of seperate queries can start to have a performance impact. I'd bundle them together just for the sake of having less PHP code to write.

Re: Which approach is faster to query

Posted: Sun Feb 17, 2008 5:20 pm
by micknc
Here is what I have now.
$query = "SELECT lineitm.SO_NO, lineitm.DESCRIP, lineitm.QTY, lineitm.NOTES, sohead.CARDHOLDER, sohead.MARKING, sohead.SHIP_VIA, sohead.ORDER_DATE, sohead.REQRD_DATE, sohead.PO_NO, custshp.NAME, custshp.E_MAIL, custshp.CONTACT, custshp.PHONE, custshp.ADDRESS2, custshp.ADDRESS, custshp.WEB_ADDR, custshp.ZIP_CODE, custshp.STATE, custshp.CITY, customrs.NAME, customrs.ADDRESS, customrs.ADDRESS2, customrs.CITY, customrs.STATE, customrs.ZIP_CODE, customrs.PHONE, customrs.FAX, customrs.CUST_ID, sohead.SO_NO FROM lineitm left Join custshp ON lineitm.SHIP_ID = custshp.SHIP_ID left Join sohead ON lineitm.SO_NO = sohead.SO_NO left Join customrs ON lineitm.CUST_ID = customrs.CUST_ID WHERE lineitm.SO_NO = $id";
It is not appreciably faster. Lineitm is what is killing me. I will start reading up on indexing. Any pointers would be appreciated.

Re: Which approach is faster to query

Posted: Sun Feb 17, 2008 5:32 pm
by Benjamin
The general rule of thumb is that any fields used in WHERE, ORDER BY and JOINS should be indexed.

So if you have a table with 300k records and your query is:

Code: Select all

 
SELECT field_one, field_two FROM my_table WHERE user_id = 10;
 
You would want to make sure the user_id field is indexed so that MySQL doesn't have to scan the entire table. It can instead look up matching records using the index.

Re: Which approach is faster to query

Posted: Sun Feb 17, 2008 5:33 pm
by micknc
Alright, I will check it out.
Thanks for all the help

Re: Which approach is faster to query

Posted: Sun Feb 17, 2008 6:46 pm
by s.dot
In my experience, using joins is considerably slower than doing two queries.

Re: Which approach is faster to query

Posted: Sun Feb 17, 2008 6:50 pm
by Benjamin
scottayy wrote:In my experience, using joins is considerably slower than doing two queries.
With or without using the JOIN statement?

Re: Which approach is faster to query

Posted: Sun Feb 17, 2008 9:36 pm
by micknc
Isn't there a function that will report the time it took the server to finish the php request? I have both copies of my form and I would love to see the outcome. I have indexed the lineitm table it seems to have helped but now I wonder which is a faster query.

Re: Which approach is faster to query

Posted: Sun Feb 17, 2008 10:42 pm
by califdon
micknc wrote:Isn't there a function that will report the time it took the server to finish the php request? I have both copies of my form and I would love to see the outcome. I have indexed the lineitm table it seems to have helped but now I wonder which is a faster query.
Yes, you can get query time, but I've forgotten the syntax--you could look it up. Another suggestion is the "explain" syntax, which is intended to show how the engine is optimizing a query, although I have always found it difficult to understand or use. Still, it's worth reading about: http://dev.mysql.com/doc/userguide/en/i ... plain.html