Is there any clever way to avoid the performance hit from using group by during fulltext search?
SELECT p.topic_id, min(p.post_id) FROM forum_posts AS p WHERE MATCH (p.post_text) AGAINST ('baby shoes' IN BOOLEAN MODE) GROUP BY p.topic_id LIMIT 20;
In this example it's fetching the lowest post_id for unique topic_ids that match the text.
With the group by to find the min, it's taking 600ms in a million row database, with about 50K rows examined.
If I remove the MIN but leave the GROUP BY, it's the same slowness, so it's the GROUP hit.
I suspect this is because it can only use one index, the fulltext ?
key: post_text | Using where; Using temporary; Using filesort Query_time: 0.584685 Lock_time: 0.000137 Rows_sent: 20 Rows_examined: 57751 Full_scan: No Full_join: No Tmp_table: Yes Tmp_table_on_disk: No Filesort: Yes Filesort_on_disk: No Merge_passes: 0
Without the GROUP BY it's 1ms so this has to be filesort speed?
(I've removed ORDER BY and everything else to isolate where the hit is)
Thanks for any insight and ideas.
(using MyISAM under mariadb if it matters)