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 Comments Leave a Comment

Daynah [ Quote ]

Very interesting. I wonder why that is. Maybe there’s less joining of tables on the second query?

March 25th, 2005 at 12:37 am
Semi Sweet [ Quote ]

Complicated. Makes me want to take at least an intro computer science class. :biggrin:

March 25th, 2005 at 1:34 am
Cine [ Quote ]

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?

March 25th, 2005 at 7:20 pm
dodo [ Quote ]

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.

March 25th, 2005 at 7:54 pm
Dianna [ Quote ]

interesting! i would have used the first… thanks for the tip!

March 26th, 2005 at 11:51 am
Cine [ Quote ]

Oooh, okay, I see. I didn’t know that, thanks! :D

March 29th, 2005 at 8:15 am

Leave a Comment Name, email and comment are required.
Email will never be displayed.

Leave a Comment
  1. :biggrin: :blank: :blush: :bored: :confused: :cool: :down: :evil: :frown: :grin: :grr: :laidback: :left: :mad: :right: :sad: :secret: :shock: :smile: :stress: :tongue: :up: :wink: :yawn: :cute: :dead: :film: :floppy: :glasses: :grumpy: :headphones: :heart: :lightbulb: :ouch: :tv: :upsidedown: :vodka: :yell: :yuck: :yummy: