MySQL Indexes?

[paulnaj]

Can someone give me any insight into how to go about using indexes in MySQL?

I use php/mysql to run my illustration agency's website (have a look if you want). The user can currently select keywords from five select boxes (artist, subject ,etc) and return pages of thumbnails.

The site works fine (we've had a lot of positive comment in the industry), but there are getting to be quite a lot of images on the site now, and I think it's needs some tuning up.

I know pretty much nothing about indexes :bemused: except primary keys. I have a couple of books, have read some tutorials, but can't seem to get a good 'handle' on them. I usually find how to implement them, but not where or when.

Has any one got any good ways to explain how I can use them (effectively)?

For example, is there a way I could speed things up using indexes on the scheme below?

---

Suppose we only were considering the following relationships (I've tried to simplify it down to the essentials for this question ... they are more involved than this!!) ...

1. Each artist can have many images
   Each image has only one artist

2. Each image can have many subjects
   Each subject can have many images
   [/list=1]
   So, I've got the following tables ...

   • artist: id (p), name

   • image: id (p), artist_id (f)

   • subject: id (p), value

   • image_subject: image_id (f), subject_id (f)

   ... where (p) means primary and (f) means foreign.

   Then, given a user selecting subject no. 24 (car) from the selection box, I would use the following simply SQL query to get the thumbnail id's.

   SELECT image.id, artist.name
   FROM image, image_subject, artist
   WHERE image.artist_id = artist.id
   AND image.id = image_subject.image_id
   AND image_subject.subject_id = 24
   ORDER BY img_id

   (Notice I don't use the subject table directly in this query, but do for others).

   So, would indexes help me at all? What sort? Should I apply them to the 'name'/'value' fields or the foriegn fields, or both?

   If more info is needed, let me know.

   Cheers,
   Paul.

[psycorpse]

indexs would help alot. You will need to index fields the are in the where statement. For instance...

SELECT image.id, artist.name
FROM image, image_subject, artist
WHERE image.artist_id = artist.id
AND image.id = image_subject.image_id
AND image_subject.subject_id = 24
ORDER BY img_id

so you would maybe want to create indexs on
image.artist_id, image_subject.image_id, and image_subject.subject_id

You can create these indexes like this

CREATE INDEX index_name ON table_name ( field_name);

replace index_name with a name that describes the index
table_name = the name of your table
field_name = the name of the column from that table

I hope that this helps.

[paulnaj]

Thanks psycorpse, that is very helpful.

So ...

You will need to index fields the are in the where statement.

... is a good general rule?

Cheers,
Paul

[tsinka]

Hi,

in your first post you wrote

Has any one got any good ways to explain how ...

Most RDBMS have a good way to explain how indexes are used. In case of MySQL you can do the following:

EXPLAIN <select_query>

That will return records which explain how MySQL optimizes the query and which indexes it will use.

If you want to create an index on text columns (e.g. varchar) it often is sufficient to create only a partial index on that columns (e.g. create and index using the first 15 characters of the column):

CREATE INDEX idxname ON atable (field(15))

You

To create an index on every column you use in a WHERE clause sometimes has a negative impact on the query speed especially if you have e.g. columns that only hold two different values like 1 and 0. MySQL might decide not to use the index in this case if even using the index would make it neccessary to scan a very large percentage of the table. The reason for that is that a whole scan of the table would require much fewer seeks than a scan using the index.

Some links:

EXPLAIN syntax
MySQL optimization

google around for tutorials like

this one (rather old but gives you the basics)

Thomas

[paulnaj]

Thomas,

Thanks for that ... I'll do some testing with the EXPLAIN statement and see how I get on with the tutorials.

I'll come back for help if necessary.

Cheers,
Paul.