0

I tried to turn on MYSQLI_REPORT_ALL | MYSQLI_REPORT_STRICT and now MySQL tells me:

No index used in query/prepared statement...

in some of my app's queries.

Is it really best practice to index every WHERE column I'll possibly use so that MySQL will not complain? Seems a bit overkill to me as there are probably some columns where I'll only use the WHEREclause occasionally.

  • 1
    Well, a query can only use one index at a time, so it's definitely overkill. If you have a query where only the arguments change (but not the columns filtered on) then creating an index on those columns may be beneficial, depending on a host of factors. But build your query(ies) first, then build index(es) to support them. – Jonathan Fite Jun 23 at 2:14
1

Turn on those flags only for initial development. They are too noisy with false alarms otherwise.

There are valid cases for queries that do not use indexes.

There are cases (such as EAV) where it is impractical to have indexes for every possible query.

I prefer to turn on the slowlog with a low value for long_query_time, and later gather what queries are the "worst". This is a much better list of queries to work on improving. It may include some slow queries with the best possible index, and exclude some very fast queries that don't involve any index.

Do not blindly index every column. Learn about "composite" indexes.

See my Cookbook on creating optimal indexes for basic queries.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.