Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question
add comment

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:

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

share|improve this answer
    
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 '13 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 –  Thomas Jones-Low Feb 24 '13 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 '13 at 2:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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