22 Working with multiple

Indexing columns

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 33 seconds on the slow 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  under  a  second  on  this

computer. That's over thirty times as fast. If this were a web page, that would be critical.

Conflicting column names

Let's take another look at that query.

select song, songs.artist, songs.album, purchasedate, songs.rating/10 as

"Song Rating", albums.rating as "Album Rating"

from songs, albums

where songs.album=albums.album

order by artist

limit 10