Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mysql covering vs composite vs column index

In the following query

SELECT  col1,col2 FROM    table1 WHERE   col3='value1'   AND   col4='value2' 

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

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 use both indexes ?

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:

SELECT  col1,col2 FROM    table1 WHERE   col3='value1' 

Lastly, Is it better to just use Covering indexes in all cases ? and does it differ between MYISAM and innodb storage engines ?

like image 530
Songo Avatar asked Nov 21 '11 14:11

Songo


People also ask

What is the difference between composite index and covering index?

when we create index then we can mention multiple column name and that is called composite index but when we create cover index then we create index on one column and for cover index we mention other column in include function.

What is covering index in MySQL?

Covering Indexes in MySQL A covering index is a special kind of index in InnoDB. When a covering index is in use, all the required fields for a query are included, or “covered”, by the index meaning that you can also reap the benefits of reading only the index instead of the data.

What is composite indexing in MySQL?

MySQL can create composite indexes (that is, indexes on multiple columns). An index may consist of up to 16 columns. For certain data types, you can index a prefix of the column (see Section 8.3. 5, “Column Indexes”).

What is index and composite index?

A composite index is a statistical tool that groups together many different equities, securities, or indexes in order to create a representation of overall market or sector performance. Typically, the elements of a composite index are combined in a standardized way so that large amounts of data can be presented easily.


2 Answers

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.

like image 141
Johan Avatar answered Sep 27 '22 15:09

Johan


I upvoted Johan's answer for completeness, but I think the following statement he makes regarding secondary indexes is incorrect and/or confusing;

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. 

While I agree the primary key is INCLUDED in the secondary index, I do not agree MySQL "will always use a covering index" in the SELECT query specified here.

To see why, note that a full index "scan" is always required in this case. This is not the same as a "seek" operation, but is instead a 100% scan of the secondary index contents. This is due to the fact the secondary index is not ordered by the primary key; it is ordered by "indexed_field" (otherwise it would not be much use as an index!).

In light of this latter fact, there will be cases where it is more efficient to "seek" the primary key, and then extract indexed_field "from the actual table," not from the secondary index.

like image 41
Sam T Avatar answered Sep 27 '22 16:09

Sam T