Mysql covering vs composite vs column index

by

A covering index is not the same as a composite index.

If I have 2 separate indexes one on col3 and the other on col4, Which one of them will be used in this query ?

The index with the highest cardinality.
MySQL keeps statistics on which index has what properties.
The index that has the most discriminating power (as evident in MySQL’s statistics) will be used.

I read somewhere that for each table in the query only one index is used. Does that mean that there is no way for the query to used both indexes ?

You can use a subselect.
Or even better use a compound index that includes both col3 and col4.

Secondly, If I created a composite index using both col3 and col4 together but used only col3 in the WHERE clause will that be worse for the performance? example:

Compound index
The correct term is compound index, not composite.
Only the left-most part of the compound index will be used.
So if the index is defined as 

index myindex (col3, col4)  <<-- will work with your example.
index myindex (col4, col3)  <<-- will not work.

See: http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html

Note that if you select a left-most field, you can get away with not using that part of the index in your where clause.
Imagine we have a compound index 

Myindex(col1,col2)

SELECT col1 FROM table1 WHERE col2 = 200  <<-- will use index, but not efficiently
SELECT * FROM table1 where col2 = 200     <<-- will NOT use index.

The reason this works is that the first query uses the covering index and does a scan on that.
The second query needs to access the table and for that reason scanning though the index does not make sense.
This only works in InnoDB.

What’s a covering index
A covering index refers to the case when all fields selected in a query are covered by an index, in that case InnoDB (not MyISAM) will never read the data in the table, but only use the data in the index, significantly speeding up the select.
Note that in InnoDB the primary key is included in all secondary indexes, so in a way all secondary indexes are compound indexes.
This means that if you run the following query on InnoDB:

SELECT indexed_field FROM table1 WHERE pk = something

MySQL will always use a covering index and will not access the actual table. Although it could use a covering index, it will prefer the PRIMARY KEY because it only needs to hit a single row.

More Related Contents:

  1. When should I use a composite index?
  2. Mysql::Error: Specified key was too long; max key length is 1000 bytes
  3. Mysql Improve Search Performance with wildcards (%%)
  4. Does Order of Fields of Multi-Column Index in MySQL Matter
  5. Does a UNIQUE constraint automatically create an INDEX on the field(s)?
  6. MySQL optimizing INSERT speed being slowed down because of indices
  7. MySQL – why not index every field?
  8. How can I make SQL case sensitive string comparison on MySQL?
  9. 1052: Column ‘id’ in field list is ambiguous
  10. Count the occurrences of DISTINCT values
  11. Is the primary key automatically indexed in MySQL?
  12. Cumulative sum over a set of rows in mysql
  13. Set NOW() as Default Value for datetime datatype?
  14. Add Auto-Increment ID to existing table?
  15. MySQL SELECT DISTINCT multiple columns
  16. SQL DELETE with JOIN another table for WHERE condition
  17. mysql trigger stored trigger is already used by statement which invoked stored trigger
  18. DOs and DONTs for Indexes [closed]
  19. Error Dropping Database (Can’t rmdir ‘.test\’, errno: 17)
  20. Using group by on two fields and count in SQL
  21. MySQL error 1241: Operand should contain 1 column(s)
  22. How to transpose rows to columns with large amount of the data in BigQuery/SQL?
  23. Mysql: Order by like?
  24. Is there a way to get the row number in Mysql like the rownum in oracle [duplicate]
  25. How to get a list of months between two dates in mysql
  26. How to format number with “.” as thousands separator and “,” as decimal separator?
  27. Using DISTINCT and COUNT together in a MySQL Query
  28. SQL (MySQL) vs NoSQL (CouchDB) [closed]
  29. How to convert a varchar column type to date type without losing the dates
  30. MySQL Multiple Joins in one query?

Leave a Comment