On 10/10/2007, Ryan McKinley <[EMAIL PROTECTED]> wrote: > > Without seeing the actual queries that are slow, it's difficult to > determine > > what the problem is. Have you tried using EXPLAIN ( > > http://dev.mysql.com/doc/refman/5.0/en/explain.html) to check if your > query > > is using the table indexes effectively? > > > > Yes, the issue is with the number of rows with 10M rows, select(*) can > take > 1 min. With 10M rows, it was actually faster to remove the index > so that it was forced to do a single iteration through all docs rather > then use the index (I don't fully understand why) > > EXPLAIN says it is a simple query using the primary key, but can still > take >30sec to complete! > > In general it seems like a bad idea to have mysql tables with lots of > rows... that is why i'm leaning towards a solr solution. >
MySQL shouldn't really have any problem working with tables having 10M+ rows (especially with simple select queries), most likely the issues you are experiencing are a result of memory limits set in the mysql conf. If you want to persevere a little longer, try increasing the values of "innodb_additional_mem_pool_size" and "innodb_buffer_pool_size" in your my.cnf config file (see http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html for more info). If there is no compelling reason for sticking with a RDBMS then maybe the solr solutions iisted above might be better. Piete
