{question}
How can I tell if my table stats/statistics are outdated?
{question}
{answer}
The very first thing we check on an under-performant query is whether the statistics on the tables involved in the query are the latest or not.
If the statistics are not collected or are old/stale, the optimizer won't be able to conclude the most optimal execution plan for a query, resulting in the possibility of the query taking longer to execute.
In SingleStore, we have the automatic statistics feature, which lets the tables' statistics be gathered automatically without manual intervention.
If we have to find out whether stats are out of date, then we can use the below methods:
1. EXPLAIN on a query shows a warning that the statistics are outdated.
2. The LAST_UPDATED column in the column statistics table information_schema.OPTIMIZER_STATISTICS shows the timestamp at which stats were last updated for a given column.
QUERY:
select DISTINCT TABLE_NAME, LAST_UPDATED
from information_schema.optimizer_statistics
where DATABASE_NAME = <database_name>
AND table_name in (<name of the tables in the query>);
If required, statistics can be gathered manually using ANALYZE command.
{answer}