Pure-Essence.Net

the mystery of querying

March 24th, 2005

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