[thelist] Indexing and/or Query Optimisation in MySQL

Joshua Olson joshua at alphashop.net
Mon Feb 4 08:53:01 CST 2002


----- Original Message -----
From: "John Handelaar" <john at userfrenzy.com>
Subject: [thelist] Indexing and/or Query Optimisation in MySQL


> The problem:  I can't figure out how to add a useful index
> for a query like this one:
>
> SELECT UID, Title, Text, Subject, AgeRating, ReadTimes, StatusFlag,
StorySet
> from stories
> where AgeRating IN ('U', 'PG', '12')
> ORDER BY Title;

The useful index in this case would be one on AgeRating and Title.  The
fields in the select part of the statement are not the fields that you
should examine when trying to use indexes to optimize a select statement...
you only need look at the WHERE clause and the ORDER BY clause.

I'm not really privy on MySQL syntax, but if I was going to do this is my
database, I would use:

CREATE NONCLUSTERED INDEX idx_stories01
ON stories (AgeRating, Title)

FWIW, You could gain even more advantage by using a numeric field instead of
a text field for AgeRating.  For example, 0 = 'U', 1 = 'G', 2 = 'PG', etc.

HTH,
-joshua






More information about the thelist mailing list