[thelist] Indexing and/or Query Optimisation in MySQL

rudy r937 at interlog.com
Mon Feb 4 09:04:05 CST 2002


> SELECT UID, Title, Text, Subject, AgeRating
>            , ReadTimes, StatusFlag, StorySet
>    from stories
>     where AgeRating IN ('U', 'PG', '12')
>      ORDER BY Title;

hi john

compound indexes do not help -- in fact they hinder -- unless the WHERE
clause contains predicates on one or more index columns *beginning from
highest to lowest*

think of the index as a sorted list

for example, if you had a table of people and a coumpound index on IQ *and*
Age (in that order), the index will be used if the WHERE clause tests
IQ=foo and Age=bar, the index *might* be used if the WHERE clause tests
only IQ=foo, but the index will probably *not* be used if the WHERE clause
tests only Age=bar, because then the database would have to scan the entire
index finding Age=bar (because in the compound index, Age=bar can be all
over the place), so it might as well just scan the table

make sense?

that said, you'll probably want to run

      alter table stories
             add index AgeRatingindex (AgeRating)

adding an index on Title does not help improve sorting, but might improve
searching

adding an index on UID, for when you grab individual rows, might also be
worthwhile, unless -- and please forgive my lack of experience with
mysql -- it's already a primary key and has an associated unique index (do
you have to declre pk indexes separately in mysql?  in other databases it's
automatic)


rudy





More information about the thelist mailing list