[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