Hi Jens,
>Is there any possibility to watch a value, if the query would use an index or not
It's a bit of a hack, but I guess you could just run the EXPLAIN every so often and if/when you notice a table scan being preferred run ANALYZE TABLE and see if that helps. (Or just run ANALYZE TABLE before you run your queries if it's necessary) It should be easy enough to automate.
Does your database start empty each day? Typically incorrect index stats only become problematic when your data distribution changes vastly.
>(I think the statistics are optimized for the index and update statements)?
They are updated, but they don't seem to be particularly accurate afterwards. TBH I'm not sure exactly how InnoDB or MyISAM modify their index stats based on the results of DML statements.
It looks like an ANALYZE TABLE before you run the queries is the way to go, and it's super quick on InnoDB so it shouldn't be too much hassle.
Note that on Innodb ANALYZE TABLE isn't particularly accurate either: See:
http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html
Actually, could someone on this forum explain (or point me at documentation) which details exactly how and when index stats are updated as a result of DML/server stops or starts/analyze/optimize/alter table/etc, for both InnoDB and MyISAM? I've not found anything that's totally clear on this - aside from a few snippets on mysqlperformanceblog.com, eg:
http://www.mysqlperformanceblog.com/2006/07/30/mysql-crash-recovery/ (see bottom of post)
(And right now I'm feeling too tired to look at the source)
Cheers,
Toasty
-----------------------------------------
email: 'toasty'*3 at gmail