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

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)

1 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:


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

  • I understand that implication but doesn't the Tmp_table_on_disk: No in the slow query log indicate it's being done purely in memory? Or does that flag not catch when it exceeds the size? I know the flag works for when there are text fields in the select.
    – ck_
    Feb 24, 2013 at 15:37
  • MariaDB uses a storage engine called Aria for the internal temporary tables. I've seen some posts indicate that for some queries it may not be as fast as MyISAM used by stock MySQL. But that depends upon your version of MariaDB and the schema you are using: kb.askmonty.org/en/benchmarking-aria Feb 24, 2013 at 18:34
  • Thomas, I believe Aria is only used when it has to use disk based temporary tables, tmp tables that are in memory use the same method as mysql. We had similar performance issue under mysql, sorry to complicate the question with mariadb, it was meant to see if it had an extra solution over mysql.
    – ck_
    Feb 25, 2013 at 2:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.