Which SELECT statement is faster?

Forum Moderators: open

Message Too Old, No Replies

Which SELECT statement is faster?

HoboTraveler

9:43 am on Jan 16, 2007 (gmt 0)

Hello,

Since I do not delete any rows in the table, I have a field called 'enabled'. So when enabled=1, the row is enabled and when enabled=0, the row is flagged as disabled or deleted.

To SELECT rows from the table, I do a SELECT name FROM table WHERE enabled = '1' AND name='foo'

My question is, would it be faster if I changed the above statement to SELECT name FROM table WHERE name='foo' AND enabled = '1'

Is it faster if I grab all the enabled rows first and then look for the specific name or is it faster if I select the name first and then the enabled.

The table has about a million entries. Both fields are indexed. The enabled is an INT type and name is a varchar.

DB is MySQL
Language is PHP 4 on Linux

TIA

[edited by: HoboTraveler at 9:45 am (utc) on Jan. 16, 2007]

phranque

11:07 am on Jan 16, 2007 (gmt 0)

my instinct says checking the int field first would be a faster select.
why don't you just test the two queries?
i often use the command line to test elapsed time for queries:

date; msyql -udbuser -pdbpassword -e"SELECT name FROM table WHERE enabled = '1' AND name='foo'" dbname; date

LifeinAsia

4:38 pm on Jan 16, 2007 (gmt 0)

First, change the enabled field from a CHAR field to BIT. Second, create an index on the enabled field.

This should speed things up much more than simply changing the order.

fischermx

7:29 pm on Jan 16, 2007 (gmt 0)

If a DBMS is dependent on the columns order in a where clause for the task of picking a proper index, please put it right on to the trash can and get something decent.

Be sure you have an index on the Enabled column.
If the search is too frecuent and intensive, consider an index in both "name"+"enabled" columns.

moltar

8:28 pm on Jan 16, 2007 (gmt 0)

Second fischermx. All good database engines should optimize such queries with no questions asked.

jatar_k

8:58 pm on Jan 16, 2007 (gmt 0)

as a bit of an aside, you could have a script that takes all of your disabled rows and moves them into another table. This way you could reduce the active size and remove the need for the 2 parts to every query.