Can MySQL use multiple indexes for a single query?

by

Yes, MySQL can use multiple index for a single query. The optimizer will determine which indexes will benefit the query. You can use EXPLAIN to obtain information about how MySQL executes a statement. You can add or ignore indexes using hints like so:

SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX FOR ORDER BY (i2) ORDER BY a;

I would suggest reading up on how MySQL uses indexes.

Just a few excerpts:

If there is a choice between multiple indexes, MySQL normally uses the
index that finds the smallest number of rows.

If a multiple-column index exists on col1 and col2, the appropriate
rows can be fetched directly. If separate single-column indexes exist
on col1 and col2, the optimizer will attempt to use the Index Merge
optimization (see Section 8.2.1.4, “Index Merge Optimization”), or
attempt to find the most restrictive index by deciding which index
finds fewer rows and using that index to fetch the rows.

More Related Contents:

  1. With MySQL, how can I generate a column containing the record index in a table?
  2. How do I add indexes to MySQL tables?
  3. Differences between INDEX, PRIMARY, UNIQUE, FULLTEXT in MySQL?
  4. When should I use a composite index?
  5. Is the primary key automatically indexed in MySQL?
  6. MySQL indexes – what are the best practices?
  7. Mysql::Error: Specified key was too long; max key length is 1000 bytes
  8. How to see indexes for a database or table in MySQL?
  9. Speeding up row counting in MySQL
  10. What is cardinality in MySQL?
  11. MySQL varchar index length
  12. What’s the difference between using INDEX vs KEY in MySQL?
  13. Mysql Improve Search Performance with wildcards (%%)
  14. How can I manipulate MySQL fulltext search relevance to make one field more ‘valuable’ than another?
  15. DOs and DONTs for Indexes [closed]
  16. MySQL performance: multiple tables vs. index on single table and partitions
  17. Mysql covering vs composite vs column index
  18. Is there any performance gain in indexing a boolean field?
  19. How to disable index in innodb
  20. Higher cardinality column first in an index when involving a range?
  21. Does Order of Fields of Multi-Column Index in MySQL Matter
  22. Create an index on a huge MySQL production table without table locking
  23. MySQL Integer vs DateTime index
  24. Does a UNIQUE constraint automatically create an INDEX on the field(s)?
  25. MySQL 5.0 indexes – Unique vs Non Unique
  26. How to fix MySql: index column size too large (Laravel migrate)
  27. MySQL optimizing INSERT speed being slowed down because of indices
  28. Would it help to add index to BIGINT column in MySQL?
  29. MySQL: how to index an “OR” clause
  30. Mysql performance on 6 million row table

Leave a Comment