MySQL Forums
Forum List  »  Performance

Re: Poor Performance with tables over 1Million entries
Posted by: József Rekedt-Nagy
Date: April 10, 2009 01:50PM

Rick James Wrote:
-------------------------------------------------------
> What is innodb_buffer_pool_size? It should be
> around 70% of available RAM.
Tried with 512MB and 1512MB as well, made ~no difference in the query speed.

>
> 46 rows (estimated) for doing that query -- that
> is nice. Sounds as if it did the OR efficiently.

The final answer is only 3 rows, but all the OR matches many more rows on their own, up to 10K for each of them.

First of all, I do believe that this is quite a simple query, the application would be dealing with MUCH more complex ones, with adder permissions, ordering and so on.
Also, it is just _1_Million of rows, is it really 'normal' for MySql to suffer at this amount? Can't believe it :|

> Since it needs to read about that many rows to get
> the answer, you aren't going to get it much faster
> with out overhauling your schema and application.

What schema would you suggest? Horizontal partitioning?
Can't really think of anything else, and to be honest, doing that at 1M entries _because of the amount_ just feels strange. Would mean a crazy amount of (real or partitioned) tables in no time.
Application level changes r fine, so if you have any ideas, pls shoot :)


> "Using index" does not mean what you might think.
> It means that the query was satisfied by using
> ONLY the index for that table. That is, it did
> not need to reach over into the data. This
> happens with it has an index containing ALL the
> fields in the SELECT fields, WHERE, ORDER BY,
> etc.
Yeah, that's correct.

>
> If you are referring to SELECT * on that mapping
> table, I agree that it is strange -- seems like it
> should discover that it could be "using index".
> Perhaps it is, but fails to say so??
It does, so that's fine yeah.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.