Mysql WHERE statements priority during execution

Question

Hello,

SELECT * FROM articles WHERE title LIKE '%query%' AND user_id=123

The user_id column is index. How Mysql will execute this query? I think, that LIKE have lowest priority, right?

Thank you.

Answer 1

EXPLAIN SELECT * FROM articles WHERE title LIKE '%query%' AND user_id=123

That will tell it all :)

Answer 2

MySQL will almost certainly use the user_id index.

For queries in general, the optimizer will work out the possible access paths and use some pre-computed statistics on the table contents to estimate which will be the quickest.

For example, if your articles tables also had a date column article_date that was indexed and you executed a query with predicates on both user_id and article_date. Then MySQL will have to estimate which index will be the quickest to select the required rows.

If there are thousands of articles a day by lots of different users, then the user_id index might be best. But if there are only a few articles a day, but most users post a large number of articles, it may be quicker to use article_date index instead.