If you have large databases, or if you have complex select statements, searches can be slow. This is because MySQL has to re-index the columns every time you execute the statement. If you are willing to slow down adding data, however, you can pre-index columns. Generally, you don’t want to pre-index any column that is not searched on, related to, or sorted by.
That last query took 545 milliseconds on the computer I’m using to test this. We are sorting it by artist and checking the songs album against the albums album. Let’s add an index for each of those three columns and see if that speeds things up.
There are three basic kinds of indexes you’ll use most often: a primary index, a unique index, and a normal, non-unique index.
A primary index is a unique index, and is usually your id field. You can only have one primary index per table.
Since artists and album names can occur more than once, make all of these be an “index”, a non-unique index.
Here is how the index area of your Structure tab should look for the songs table. Your albums table should look the same, but without the artist index.
After making these three indexes, the same query now took 0.7 milliseconds on this computer. That’s over seven hundred times as fast. If this were a web page, that would be critical.
