best query for mysql speed

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
sn4k3
Forum Commoner
Posts: 37
Joined: Tue Oct 16, 2007 3:51 pm

best query for mysql speed

Post by sn4k3 »

what will be more fast to mysql execute

DB: InnoDB
Primary Index: id

Code: Select all

SELECT * FROM table WHERE id = some_int LIMIT 1ORSELECT * FROM table LIMIT some_int , 1
Thanks
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

Re: best query for mysql speed

Post by Darhazer »

You always can test them both and compare the results. It's not that hard. The first one should be faster.

And by the way, the 2 queries can return different row... You do not guarantee in any way that with the second you will retrieve the record with id = some_int
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: best query for mysql speed

Post by Benjamin »

They would be the same speed unless a field in the where clause is indexed and reduced the cardinality. (The number of records to check). In that case, the first query would be faster. If the field in the where clause is NOT indexed and MySQL needs to perform a table scan the second query would be faster. Also just to clarify, by same speed I mean the difference wouldn't make a difference, performance wise.
Post Reply