Query Structure - Does the order matter?

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
Swede78
Forum Contributor
Posts: 198
Joined: Wed Mar 12, 2003 12:52 pm
Location: IL

Query Structure - Does the order matter?

Post 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.
[]InTeR[]
Forum Regular
Posts: 416
Joined: Thu Apr 24, 2003 6:51 am
Location: The Netherlands

Post 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.
Post Reply