[thelist] Indexing and/or Query Optimisation in MySQL

John Handelaar john at userfrenzy.com
Mon Feb 4 09:38:01 CST 2002


> -----Original Message-----
> From: thelist-admin at lists.evolt.org
> [mailto:thelist-admin at lists.evolt.org]On Behalf Of rudy
> Sent: 04 February 2002 15:07
> To: thelist at lists.evolt.org
> Subject: Re: [thelist] Indexing and/or Query Optimisation in MySQL
>
> hi john
>
> 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?

Yeah - and thanks for replying.  My main problem here is that
even with an index on AgeRating a query (even with LIMIT 10)
on the >13,000 rows is coming through at 30 seconds or more.

Sorry to tax you again but you *are* the man :-)


> 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)

And in this case too.

Cheers

------------------------------------------
John Handelaar

T +44 20 7209 4117       M +44 7930 681789
F +44 870 169 7657   E john at userfrenzy.com
------------------------------------------




More information about the thelist mailing list