I did tried it again by experimenting different values for innodb_stats_persistent_sample_pages and run analyze table few times, different values for LIMIT clause but no luck so far. EXPLAIN output shows query always uses PRIMARY KEY.
Data distribution may be important here. Can you please show me output of
mysql> SELECT type, COUNT(type) FROM suggestion GROUP BY type;
mysql> SELECT rejected, COUNT(rejected) FROM suggestion GROUP BY rejected;
mysql> SELECT flags, COUNT(flags) FROM suggestion GROUP BY flags;
And
mysql> SHOW KEYS FROM suggestion;
Also, may be you can try to run ANALYZE TABLE and check if it improves query execution path.
I did tried it again by experimenting different values for innodb_ stats_persisten t_sample_ pages and run analyze table few times, different values for LIMIT clause but no luck so far. EXPLAIN output shows query always uses PRIMARY KEY.
Data distribution may be important here. Can you please show me output of
mysql> SELECT type, COUNT(type) FROM suggestion GROUP BY type;
mysql> SELECT rejected, COUNT(rejected) FROM suggestion GROUP BY rejected;
mysql> SELECT flags, COUNT(flags) FROM suggestion GROUP BY flags;
And
mysql> SHOW KEYS FROM suggestion;
Also, may be you can try to run ANALYZE TABLE and check if it improves query execution path.