Mysql WHERE statements priority during execution_问答_开发者

Mysql WHERE statements priority during execution

开发者 https://www.devze.com 2023-01-10 18:59 出处：网络

SELECT * FROM articles WHERE title LIKE \'%query%\' AND user_id=123 开发者_Python百科 The user_id column is index. How Mysql will execute this query? I think, that LIKE have lowest priority, right?

相关专题：

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

开发者_Python百科

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

Thank you.

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

That will tell it all :)

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.