Page 1 of 1

Query Structure - Does the order matter?

Posted: Tue Apr 22, 2003 5:55 pm
by Swede78
I have a pretty simple question, that hopefully has a simple answer.

I just wanted to know if the order in which you structure your query affects the speed of the query in a MySQL database.

Let's say I have a (simplified for ease of explanation) database with 10,000 records. There are 3 fields; FirstName, LastName, and Category. Each 'full name' combo is unique and there are 5 unique categories with an equal number of records each. Now if I were to set up a search that looks for a specific first and last name that is in 1 of the 5 categories, which of the following queries would be faster?

Query A:
SELECT FirstName, LastName, Category
FROM table
WHERE FirstName Like '%varFirst%' AND LastName Like '%varLast%' AND Category = '3of5'

Query B:
SELECT Category, FirstName, LastName
FROM table
WHERE Category = '3of5' AND FirstName Like '%varFirst%' AND LastName Like '%varLast%'

I would guess that Query B is faster, possibly because it reduces the records to 2,000 immediately, then searches those for the names.

Does the order matter in the SELECT clause, WHERE clause, both, or neither?

I don't know of any simple way to time the results myself, otherwise I'd just try both.

Posted: Thu Apr 24, 2003 7:26 am
by []InTeR[]
You can test what query is the fastest by a other query :)

Code: Select all

SELECT BENCHMARK(10000,SELECT * FROM USER)
If you will do this query an mysqlclient the 'result' will by 0. But the inportent value of the benchmark is the execution time.

I use this function to test my indexes if i have more then one.