MySQL not using indexes with WHERE IN clause?

by

See How MySQL Uses Indexes.

Also validate whether MySQL still performs a full table scan after you add an additional 2000-or-so rows to your user_metrics table. In small tables, access-by-index is actually more expensive (I/O-wise) than a table scan, and MySQL’s optimizer might take this into account.

Contrary to my previous post, it turns out that MySQL is also using a cost-based optimizer, which is very good news – that is, provided you run your ANALYZE at least once when you believe that the volume of data in your database is representative of future day-to-day usage.

When dealing with cost-based optimizers (Oracle, Postgres, etc.), you need to make sure to periodically run ANALYZE on your various tables as their size increases by more than 10-15%. (Postgres will do this automatically for you, by default, whereas other RDBMSs will leave this responsibility to a DBA, i.e. you.) Through statistical analysis, ANALYZE will help the optimizer get a better idea of how much I/O (and other associated resources, such as CPU, needed e.g. for sorting) will be involved when choosing between various candidate execution plans. Failure to run ANALYZE may result in very poor, sometimes disastrous planning decisions (e.g. millisecond-queries taking, sometimes, hours because of bad nested loops on JOINs.)

If performance is still unsatisfactory after running ANALYZE, then you will typically be able to work around the issue by using hints, e.g. FORCE INDEX, whereas in other cases you might have stumbled over a MySQL bug (e.g. this older one, which could have bitten you were you to use Rails’ nested_set).

Now, since you are in a Rails app, it will be cumbersome (and defeat the purpose of ActiveRecord) to issue your custom queries with hints instead of continuing to use the ActiveRecord-generated ones.

I had mentioned that in our Rails application all SELECT queries dropped below 100ms after switching to Postgres, whereas some of the complex joins generated by ActiveRecord would occasionally take as much as 15s or more with MySQL 5.1 because of nested loops with inner table scans, even when indices were available. No optimizer is perfect, and you should be aware of the options. Other potential performance issues to be aware of, besides query plan optimization, are locking. This is outside the scope of your problem though.

More Related Contents:

  1. How to Optimize Queries in a Database – The Basics
  2. MySQL query finding values in a comma separated string
  3. In MySQL queries, why use join instead of where?
  4. SQLite INSERT – ON DUPLICATE KEY UPDATE (UPSERT)
  5. Storing JSON in database vs. having a new column for each key
  6. PostgreSQL GROUP BY different from MySQL?
  7. Can table columns with a Foreign Key be NULL?
  8. MySQL dump by query
  9. single fixed table with multiple columns vs flexible abstract tables
  10. How many rows in a database are TOO MANY?
  11. SQLite – ORDER BY RAND()
  12. SQL – How to transpose?
  13. Which SQL query is better, MATCH AGAINST or LIKE?
  14. MySQL equivalent of DECODE function in Oracle
  15. How to delete Duplicates in MySQL table
  16. Best database field type for a URL
  17. Query that ignore the spaces
  18. MySQL – how to front pad zip code with “0”?
  19. Create date from day, month, year fields in MySQL
  20. Find rows that have the same value on a column in MySQL
  21. what is the difference between GROUP BY and ORDER BY in sql
  22. How to speed up sql queries ? Indexes?
  23. How do I list all the columns in a table?
  24. Two single-column indexes vs one two-column index in MySQL?
  25. VARCHAR as foreign key/primary key in database good or bad?
  26. How to find all IDs of children recursively?
  27. MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET
  28. SQL Server indexes – ascending or descending, what difference does it make?
  29. Why do you create a View in a database?
  30. Mysql strip time component from datetime

Leave a Comment