Which approach is faster to query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
micknc
Forum Contributor
Posts: 115
Joined: Thu Jan 24, 2008 11:13 pm

Which approach is faster to query

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Which approach is faster to query

Post by Benjamin »

A single query would be better.
User avatar
micknc
Forum Contributor
Posts: 115
Joined: Thu Jan 24, 2008 11:13 pm

Re: Which approach is faster to query

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Which approach is faster to query

Post 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.
User avatar
micknc
Forum Contributor
Posts: 115
Joined: Thu Jan 24, 2008 11:13 pm

Re: Which approach is faster to query

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Which approach is faster to query

Post 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.
User avatar
micknc
Forum Contributor
Posts: 115
Joined: Thu Jan 24, 2008 11:13 pm

Re: Which approach is faster to query

Post by micknc »

Alright, I will check it out.
Thanks for all the help
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: Which approach is faster to query

Post by s.dot »

In my experience, using joins is considerably slower than doing two queries.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Which approach is faster to query

Post by Benjamin »

scottayy wrote:In my experience, using joins is considerably slower than doing two queries.
With or without using the JOIN statement?
User avatar
micknc
Forum Contributor
Posts: 115
Joined: Thu Jan 24, 2008 11:13 pm

Re: Which approach is faster to query

Post 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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Which approach is faster to query

Post 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
Post Reply