Need to refine MySQL indexes a bit

Question

I have been thinking about my database indexes lately, in the past I just casually threw them as an afterthought and never thought that they are correct or even help. I read conflicting information, some say that more indexes are better, while others say too many indexes, so I hope to get some clarification and learn a little here.

Let's say I have this hypothetical table:

CREATE TABLE widgets ( widget_id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, widget_name VARCHAR(50) NOT NULL, widget_part_number VARCHAR(20) NOT NULL, widget_price FLOAT NOT NULL, widget_description TEXT NOT NULL ); 

I would usually add an index for the fields that will be joined, and the fields that will be sorted most often:

 ALTER TABLE widgets ADD INDEX widget_name_index(widget_name); 

So, now in the request, for example:

 SELECT w.* FROM widgets AS w ORDER BY w.widget_name ASC 

widget_name_index used to sort the result set.

Now, if I add a search parameter:

 SELECT w.* FROM widgets AS w WHERE w.widget_price > 100.00 ORDER BY w.widget_name ASC 

I think I need a new index.

 ALTER TABLE widgets ADD INDEX widget_price_index(widget_price); 

But will both indices be used? As I understand it, it will not ...

 ALTER TABLE widgets ADD INDEX widget_price_name_index(widget_price, widget_name); 

Now widget_price_name_index will be used to select and order entries. But what if I want to rotate it and do it:

 SELECT w.* FROM widgets AS w WHERE w.widget_name LIKE '%foobar%' ORDER BY w.widget_price ASC 

Will widget_price_name_index be used for this? Or do I need widget_name_price_index ?

 ALTER TABLE widgets ADD INDEX widget_name_price_index(widget_name, widget_price); 

Now, if I have a search box that looks for widget_name , widget_part_number and widget_description ?

 ALTER TABLE widgets ADD INDEX widget_search(widget_name, widget_part_number, widget_description); 

But what if end users can sort by any column? It’s easy to understand how I can get more than a dozen indexes for just 5 columns.

If you add another table:

 CREATE TABLE specials ( special_id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, widget_id INT UNSIGNED NOT NULL, special_title VARCHAR(100) NOT NULL, special_discount FLOAT NOT NULL, special_date DATE NOT NULL ); ALTER TABLE specials ADD INDEX specials_widget_id_index(widget_id); ALTER TABLE specials ADD INDEX special_title_index(special_title); SELECT w.widget_name, s.special_title FROM widgets AS w INNER JOIN specials AS s ON w.widget_id=s.widget_id ORDER BY w.widget_name ASC, s.special_title ASC 

I assume this will use widget_id_index and widgets.widget_id the primary key index for the connection, but what about sorting? Will both widget_name_index and special_title_index ?

I don’t want to hang out too long, there are an infinite number of scenarios that I could discuss. Obviously, this can become much more complicated with real world scenarios, rather than with a few simple tables. Any clarification would be appreciated.

Answer 1

According to best practices, you do not need to create an index when defining table schemas. It is always better to create an index when creating queries in the application. In most cases, you start with a single column index to satisfy the query. If you want to use many columns in a query, you can create a coverage index.

A coverage index is an index with two or more columns in it. If the index meets all the requirements for a query column, the storage engine can get all the results from the index instead of kicking in the disk I / O. Thus, when creating a query that uses more columns, you can either create a new index to cover all the necessary columns, or expand the existing index to add more columns.

You should accept some considerations when doing any of the above. MySQL only considers the index if the leftmost column of the index can be used in the query. Otherwise, it just searches the entire table to get the results. Therefore, if you can expand an existing index without affecting all queries that use this index, then this would be a smart choice. Otherwise, you can continue and create a new index for the new query. Sometimes queries can be adjusted to adapt to the structure of the index.

Answer 2

The index speeds up selection, but slows down insertions and updates. You do not need to create an index for every possible combination of columns that you can imagine. Usually I just create the obvious indexes that I know I use often and add more if I see that they are needed after taking performance measurements. The database can still use the index, even if it does not cover all the columns in the query.

Answer 3

A query always uses only one index. Fortunately, you can create an index spanning multiple columns:

 ALTER TABLE widgets ADD INDEX name_and_price_index(widget_name, widget_price); 

The above index will be used if you selected widget_name or widget_name + widget_price (but not only widget_price).

As MitMaro points out, use EXPLAIN in the query to find out which MySQL indexes should choose, as well as which index it uses. See here for more details.