0

In the course of optimizing my MariaDB database I have tried the following query (found in this blocg post):

ANALYZE TABLE <my_table> PERSISTENT FOR ALL; 

The command runs without an error, however for some columns, I get in the output:

Engine-independent statistics are not collected for column '<my_column>' 

These columns are all the TEXT columns in my table. Does the command still have an effect? And does it have an effect at all on FULLTEXT indexes, particularly when used in boolean mode? The performance does not seem to be any better, or negligibly so, even when using exclusively non-TEXT columns.

1 Answer 1

0

Does the command still have an effect despite the warning about stats not collected for your TEXT columns? Yes. Stats are still collected for the other columns in the table, and these will be available to the optimiser to improve your query plans.

Does the command have an effect at all on FULLTEXT indexes? Probably not on your FULLTEXT indexes since they are presumably on those TEXT columns whose stats were not collected. If they were created on VARCHAR or CHAR columns instead, then maybe stats could be collected, and theoretically that could help the optimiser.

Be aware that the purpose of collecting the table stats is simply to help the optimiser create better query plans. It doesn't do anything to the indexes themselves to speed up queries. Better query plans means things like choosing the best index if there are multiple to choose from, or maybe in some cases omitting an index that isn't helpful. And to choose the optimal join order of the tables.

If you don't have a particularly complicated query, then it's perhaps not surprising that collecting the table stats didn't improve your query's performance. You can check what your query plan looks like using the EXPLAIN <query> statement (doesn't actually execute the query) or ANALYZE <query> statement (does execute the query which can be helpful as it gives some more interesting output).

1
  • Yes, this is basically what I have guessed. My queries most often use VARCHAR, but usually quite simple which is probably the reason performance gains are negligible. Commented Jul 22 at 7:20

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.