Two single-column indexes vs one two-column index in MySQL?

by

If you have two single column indexes, only one of them will be used in your example.

If you have an index with two columns, the query might be faster (you should measure). A two column index can also be used as a single column index, but only for the column listed first.

Sometimes it can be useful to have an index on (A,B) and another index on (B). This makes queries using either or both of the columns fast, but of course uses also more disk space.

When choosing the indexes, you also need to consider the effect on inserting, deleting and updating. More indexes = slower updates.

More Related Contents:

  1. How does database indexing work? [closed]
  2. How many rows in a database are TOO MANY?
  3. VARCHAR as foreign key/primary key in database good or bad?
  4. MySQL query finding values in a comma separated string
  5. In MySQL queries, why use join instead of where?
  6. Why would an IN condition be slower than “=” in sql?
  7. SQLite INSERT – ON DUPLICATE KEY UPDATE (UPSERT)
  8. MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET
  9. Does Foreign Key improve query performance?
  10. Importance of varchar length in MySQL table
  11. MySQL dump by query
  12. single fixed table with multiple columns vs flexible abstract tables
  13. SQLite – ORDER BY RAND()
  14. SQL – How to transpose?
  15. Which SQL query is better, MATCH AGAINST or LIKE?
  16. MySQL equivalent of DECODE function in Oracle
  17. How to delete Duplicates in MySQL table
  18. How does table alias names affect performance?
  19. Performance issue in using SELECT *? [duplicate]
  20. DOs and DONTs for Indexes [closed]
  21. Favourite performance tuning tricks [closed]
  22. SQL primary key: integer vs varchar
  23. How to Optimize Queries in a Database – The Basics
  24. How to check which locks are held on a table
  25. Does Order of Fields of Multi-Column Index in MySQL Matter
  26. Create date from day, month, year fields in MySQL
  27. Find rows that have the same value on a column in MySQL
  28. what is the difference between GROUP BY and ORDER BY in sql
  29. How do you interpret a query’s explain plan?
  30. How to find all IDs of children recursively?

Leave a Comment