Search Results for: explain using index

Multi Column indexes vs Index Merge

The mistake I commonly see among MySQL users is how indexes are created. Quite commonly people just index individual columns as they are referenced in where clause thinking this is the optimal indexing strategy. For example if I would have something like AGE=18 AND STATE=’CA’ they would create 2 separate indexes on AGE and STATE […]

3 ways MySQL uses indexes

I often see people confuse different ways MySQL can use indexing, getting wrong ideas on what query performance they should expect. There are 3 main ways how MySQL can use the indexes for query execution, which are not mutually exclusive, in fact some queries will use indexes for all 3 purposes listed here.

Using index for ORDER BY vs restricting number of rows.

One interesting problem with MySQL Optimizer I frequently run into is making poor decision when it comes to choosing between using index for ORDER BY or using index for restriction. Consider we’re running web site which sell goods, goods may be from different categories, different sellers different locations which can be filtered on, and there […]

Covering index and prefix indexes

I already wrote in the blog as well mentioned in presentation there is often a choice you have to make between having prefix index – which can be significantly smaller in size and having index being covering index, which means query can be executed using only data from the index without reading the row itself. […]