StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PONeed a little clarification on MySQL Indexes
text
Body
copied!<p>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.</p> <p>Let's say I have this hypothetical table:</p> <pre><code>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 ); </code></pre> <p>I would typically add an index for fields that will be joined and fields that will be sorted on most often:</p> <pre><code>ALTER TABLE widgets ADD INDEX widget_name_index(widget_name); </code></pre> <p>So now, in a query such as:</p> <pre><code>SELECT w.* FROM widgets AS w ORDER BY w.widget_name ASC </code></pre> <p>The <code>widget_name_index</code> is used to sort the resultset.</p> <p>Now if I add a search parameter:</p> <pre><code>SELECT w.* FROM widgets AS w WHERE w.widget_price > 100.00 ORDER BY w.widget_name ASC </code></pre> <p>I guess I need a new index.</p> <pre><code>ALTER TABLE widgets ADD INDEX widget_price_index(widget_price); </code></pre> <p>But, will it use both indexes? As I understand it it won't...</p> <pre><code>ALTER TABLE widgets ADD INDEX widget_price_name_index(widget_price, widget_name); </code></pre> <p>Now <code>widget_price_name_index</code> will be used to both select and order the records. But what if I want to turn it around and do this:</p> <pre><code>SELECT w.* FROM widgets AS w WHERE w.widget_name LIKE '%foobar%' ORDER BY w.widget_price ASC </code></pre> <p>Will <code>widget_price_name_index</code> be used for this? Or do I need a <code>widget_name_price_index</code> also?</p> <pre><code>ALTER TABLE widgets ADD INDEX widget_name_price_index(widget_name, widget_price); </code></pre> <p>Now what if I have a search box that searches <code>widget_name</code>, <code>widget_part_number</code> and <code>widget_description</code>? </p> <pre><code>ALTER TABLE widgets ADD INDEX widget_search(widget_name, widget_part_number, widget_description); </code></pre> <p>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.</p> <p>If we add another table:</p> <pre><code>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 </code></pre> <p>I am assuming this will use <code>widget_id_index</code> and the <code>widgets.widget_id</code> primary key index for the join, but what about the sorting? Will it use both <code>widget_name_index</code> and <code>special_title_index</code> ?</p> <p>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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload