Pieter Berkel wrote:
Given that the tables are of type InnoDB, I think it's safe to assume that
you're not planning to use MySQL full-text search (only supported on MyISAM
tables).
I am only using SQL for the base store - it is only accessed for
updating and generating solr documents. All search and public access id
is done via solr.
MyISAM?! I have been bit by corrupt MyISAM tables one too many times ;)
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.
