When should I use a composite index?

by

You should use a composite index when you are using queries that benefit from it. A composite index that looks like this:

index( column_A, column_B, column_C )

will benefit a query that uses those fields for joining, filtering, and sometimes selecting. It will also benefit queries that use left-most subsets of columns in that composite. So the above index will also satisfy queries that need

index( column_A, column_B, column_C )
index( column_A, column_B )
index( column_A )

But it will not (at least not directly, maybe it can help partially if there are no better indices) help for queries that need

index( column_A, column_C )

Notice how column_B is missing.

In your original example, a composite index for two dimensions will mostly benefit queries that query on both dimensions or the leftmost dimension by itself, but not the rightmost dimension by itself. If you’re always querying two dimensions, a composite index is the way to go, doesn’t really matter which is first (most probably).

More Related Contents:

  1. Mysql covering vs composite vs column index
  2. Differences between INDEX, PRIMARY, UNIQUE, FULLTEXT in MySQL?
  3. Is it possible to have function-based index in MySQL?
  4. How do MySQL indexes work?
  5. Best type of indexing when there is LIKE clause [duplicate]
  6. Does MySQL index foreign key columns automatically?
  7. What does the KEY keyword mean?
  8. Difference between Key, Primary Key, Unique Key and Index in MySQL
  9. MySQL indexes – what are the best practices?
  10. Mysql::Error: Specified key was too long; max key length is 1000 bytes
  11. What is cardinality in MySQL?
  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. How do I get MySQL to use an INDEX for view query?
  16. Is there any performance gain in indexing a boolean field?
  17. How to disable index in innodb
  18. Higher cardinality column first in an index when involving a range?
  19. MySQL load data infile – acceleration?
  20. Create an index on a huge MySQL production table without table locking
  21. MySQL Integer vs DateTime index
  22. Does a UNIQUE constraint automatically create an INDEX on the field(s)?
  23. Can MySQL use multiple indexes for a single query?
  24. MySQL 5.0 indexes – Unique vs Non Unique
  25. How to fix MySql: index column size too large (Laravel migrate)
  26. MySQL distinction between e and é (e acute) – UNIQUE index
  27. Would it help to add index to BIGINT column in MySQL?
  28. MySQL – why not index every field?
  29. Mysql performance on 6 million row table
  30. Workaround in mysql for partial Index or filtered Index?

Leave a Comment