Need a little clarification on MySQL Indexes

I've been thinking about开发者_开发知识库 my database indexes lately, in the past I just kind of non-chalantly threw them in as an afterthought, and never really put much thought into if they are correct or even helping. I've read conflicting information, some say that more indexes are better and others that too many indexes are bad, so I'm hoping to get some clarification and learn a bit 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 typically add an index for fields that will be joined and fields that will be sorted on most often:

ALTER TABLE widgets ADD INDEX widget_name_index(widget_name);

So now, in a query such as:

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

The widget_name_index is used to sort the resultset.

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 guess I need a new index.

ALTER TABLE widgets ADD INDEX widget_price_index(widget_price);

But, will it use both indexes? As I understand it it won't...

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

Now widget_price_name_index will be used to both select and order the records. But what if I want to turn it around and do this:

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 a widget_name_price_index also?

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

Now what if I have a search box that searches widget_name, widget_part_number and widget_description?

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

And what if end users can sort by any column? It's easy to see how I could end up with more than a dozen indexes for a mere 5 columns.

If we 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 am assuming this will use widget_id_index and the widgets.widget_id primary key index for the join, but what about the sorting? Will it use both widget_name_index and special_title_index ?

I don't want to ramble on too long, there are an endless number of scenarios I could conujure up. Obviously this can get much more complex with real world scenarios rather than a couple of simple tables. Any clarification would be appreciated.

By best practices, you do not have to create an index while defining the table schematics. It is always better to create an index as you create the queries in your application. In most cases, you will be starting with a single-column index to satisfy a query. If you want to use many columns in a query, you can create a covering index.

A covering index is an index with two or more columns in it. If the index satisfies all the column requirements of a query, then the storage engine can obtain all the results from the index instead of kicking in a disk I/O operation. So, when creating a query that uses more columns, you can either create a new index covering all the required columns, or, you can extend the existing index to include more columns.

You have to take some considerations while doing any one of the above. MySQL considers an index only when the left-most column of the index can be used in the query. Otherwise, it simply seeks the whole table for fetching results. So if you can extend an existing index without affecting all the queries that use that index, then it would be a wise choice. Otherwise, you can go ahead and create a new index for the new query. Sometimes, the queries can be adjusted to adapt to the index structure.

An index speeds up selects, but slows down inserts and updates. You don't need to create an index for every possible combination of columns you can imagine. I usually just create the obvious indexes that I know I will be using often, and only add more if I can see that they are needed after taking performance measurements. The database can still use an index even if it doesn't cover all the columns in the query.

Only one index is ever used in a query. Fortunately, you can create an index covering multiple columns:

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

The above index will be used if you SELECT by widget_name or widget_name + widget_price (but not just widget_price).

As MitMaro points out, use EXPLAIN on a query to see what indexes MySQL has to choose from, as well as what index it ends up using. See here for even more details.