Re: Poor Performance with tables over 1Million entries
Rick James Wrote:
-------------------------------------------------------
> WHERE, ORDER BY + LIMIT, GROUP BY -- These usually
> have to 'touch' many rows to filter / sort + limit
> / aggregrate down the the few rows you want.
>
> Example 1:
> SELECT AVG(x) FROM tbl;
> Delivers only one row, but has to touch every row.
> If x is in an index, it has to touch every row of
> that index; if x is not in an index, it has to
> touch every row in the data.
>
> Example 2:
> SELECT ... ORDER BY x LIMIT 1;
> Delivers one row, but _may_ have to read the
> entire table (ie, touch every row) to do it.
>
> Example 3 ("Get a row at random" -- unfortunately
> it is poorly optimized):
> SELECT ... ORDER BY rand() LIMIT 1;
> This will fetch every row, tack on the value of
> rand(), sort them all, then deliver 1 row.
Aha, thanks for the explanation.
Then for this typical query - where any filter matches 0-10k rows, but matching a combination of 2-3 of these all together- it will definitely need to "touch" many rows.
>
> "The relation is M-N (many to many), so any itemId
> can have many related data entries (estimation for
> avarage is 100) and a single dataId can belong to
> many itemIds (avarage here is higher, 10k or
> more)." -- This is where I got the 10K. You are
> saying that (sometimes) it has to touch 10K rows
> to decide which ones are "HAVING
> COUNT(dataId)=3".
>
> If none of 10K rows are cached in RAM, that will
> take _minutes_. If all are cached, you have a
> fighting chance to finish the query in less than a
> second. But probably not 10ms, as you desire.
>
> Solutions: (Sorry, I don't have a clear picture
> of how to implement any of these.)
> * Restructuring the data so it does not have to
> work so hard to get the answer.
Yeah..won't be obvious, data won't be normalized either..but is the only way to go it seems..
> * Summary tables.
Combination is ~endless, so can't sum it up more than this unfortunately.
> * Redefine the requirements (avoid needing the queries).
Impossible.
Rick: Thank you for your replies!
Edited 1 time(s). Last edit at 04/13/2009 03:07PM by József Rekedt-Nagy.