Avoiding performance hit from GROUP BY during FULLTEXT search?

Question

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)

Answer 1

You could try looking at tmp_table_size variable and increasing it. The group by must do a file sort using a temporary table. It's probable the temp table exceeds the size allowed and is dumped to disk for the final sort processing (very slow).

If you do:

SHOW VARIABLES LIKE 'CREATED_TMP%' 

And look at the ratio of Created_tmp_tables vs created_tmp_disk_tables, this should tell you if the value is too small.

see also: http://dev.mysql.com/doc/refman/5.0/en/internal-temporary-tables.html