How to get fastest MySQL result?

Question

I have this statement

SELECT * FROM table
where somenotuniquefield like '%\"$variable1\"%'
and primaryuniquefield = '$variable2' ";

Will this be slower than putting the unique field first? like so;

SELECT * FROM table
where primaryuniquefield = '$variable2'
and somenotuniquefield like '%\"$variable1\"%'";

It seems logical that the answer is yes but I want to know for sure before I go through and re order my MySQL statements.

you may use "EXPLAIN" before your query to see how many rows mysql reads... — Random, Apr 05 '16 at 08:09
Check it out. http://stackoverflow.com/questions/4035760/does-the-order-of-fields-in-a-where-clause-affect-performance-in-mysql — Isky, Apr 05 '16 at 08:11
@DavidColeman, I can see in your profile that you never accepted an answer. Please accept the best answer to help the StackOverflow community. Everything revolves around the points system. Please look at this page: https://meta.stackexchange.com/questions/16721/how-does-accept-rate-work/65088#65088 thank you. — ST2OD, Apr 05 '16 at 09:15

score 0 · Answer 1 · edited Aug 14 '20 at 16:25

In your example MySQL optimises the query and it will first search for the record with the primary key.

Use EXPLAIN in front of the query. This will give you some information about how the search was performed.

Another way to test if your query is performant, you can use this query:

SHOW STATUS LIKE 'last_query_cost'

(The higher the value, the more "expensive" the query is.)

If you need more data, then you can turn on profiling:

SET profiling = 1

Run your query and then show the results:

SHOW PROFILE

When measuring performance, always use SQL_NO_CACHE after the word SELECT, so it won't use the internal cache, which might not be desired.

score 0 · Answer 2 · answered Apr 05 '16 at 09:15

You dont need to manually reorder the sql statement the plan execution optimizer already does that for you.

In your case work that way. You have 2 conditions

The first one (like) has no posible index to use you should do a complete scan of the table

The second one uses a unique index so its going to return only 1 or 0 rows. Has better discrimination than the previous one so mysql it's going to use this filter always before than the like

The order its irrelevant

As suggested you can use the EXPLAIN command to check it out.

score 0 · Answer 3 · answered Aug 14 '20 at 16:41

As the other messages have pointed out that the SQL engine / optimizer will try to best apply available indexes to your query, one thing I did not see pointed out was a combination index... such as an index on your ( primaryUniqueField, someNotUniqueField ). By doing a covering index on the KEY field you KNOW will be provided, that will help the query to that finite set first.

By having the second column available in the index to (unless first is clustered and already in actual order), then the second column is also used by the query from the index pages itself and not the raw data pages that the rest of the entire record data resides. Only when a record qualifies the condition will it go back to the raw data pages to pull the rest of the columns of the query.

How to get fastest MySQL result?

3 Answers3