php code

the mystery of querying

if i know (or remember) more from my database class, i may have an answer for this. i was just coding my new board and was curious about which of the following query is faster:

SELECT m.regdate, m2.postnum FROM dmb_members m, dmb_members_post m2 WHERE m.uid=m2.uid AND m.uid = '".$userid."' LIMIT 1

or

SELECT m.regdate, m2.postnum FROM dmb_members m, dmb_members_post m2 WHERE m2.uid = '".$userid."' AND m.uid = '".$userid."' LIMIT 1

m.uid is a primary index while m2.uid is an index. what would be your guess?

using this awesome debug class, i wrote some code to compare the performance between the two queries.

the individual time that takes to run them actually varies. one may be faster than the other at one time and vice versa at another time. so i ran the script ten times and here are the results
for query:

SELECT m.regdate, m2.postnum FROM dmb_members m, dmb_members_post m2 WHERE m.uid=m2.uid AND m.uid = '".$userid."' LIMIT 1

0.01401
0.01427
0.01434
0.01394
0.01409
0.01392
0.01791
0.02065
0.02015
0.01479

average: 0.015807 seconds

for query:

SELECT m.regdate, m2.postnum FROM dmb_members m, dmb_members_post m2 WHERE m2.uid = '".$userid."' AND m.uid = '".$userid."' LIMIT 1

0.01357
0.01379
0.01361
0.01425
0.01388
0.01362
0.01378
0.02175
0.01362
0.01362

average: 0.014549 seconds.

based on these results, i conclude the second one is on average faster than the first. tho i can not explain why, at least i know which one to use now 🙂

6 thoughts on “the mystery of querying

  1. Oi, complicated. Joining tables are just too far out of my league so far. But out of curiosity – why the LIMIT 1? If the uids are unique -which they should be- is it really necessary?

    1. If you are selecting only a few rows with LIMIT, MySQL uses indexes in some cases when normally it would prefer to do a full table scan.

      form here

      it’s not about the result, it’s about optimizing and speed. you are welcome to do a test compare two queries with limit and without using that debug class. on average, with limit is always faster. again because reason above. so it’s always a good idea to use LIMIT 1 even if you don’t need it.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s