Query Structure - Does the order matter?
Posted: Tue Apr 22, 2003 5:55 pm
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.
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.