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.
Query Structure - Does the order matter?
Moderator: General Moderators
You can test what query is the fastest by a other query 
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.
Code: Select all
SELECT BENCHMARK(10000,SELECT * FROM USER)I use this function to test my indexes if i have more then one.