Geek asks and answers

Optimize counter and select query

I am using MySQL 5.5. I have a query (full text) that uses a subquery. To help performance and the fact that I use pagination, I use LIMIT to limit the number of results.

SELECT * 
FROM ( 
    SELECT id, type, type_id, content, MATCH(content) AGAINST('john') as relevance, IFNULL  (parent_type, UUID()) as parent_type, IFNULL(parent_id, UUID()) as parent_id 
    FROM search_index
    WHERE MATCH(content) AGAINST('john*' IN BOOLEAN MODE) GROUP BY parent_type, parent_id ) as search 
GROUP BY search.type, search.type_id DESC LIMIT 10;

The fact is that in addition to this, I need to send the total number of possible results, for example (50,000), with each search query. To get the bill, I use:

SELECT COUNT(*) FROM(
    SELECT * 
    FROM ( 
        SELECT id, type, type_id, content, MATCH(content) AGAINST('john') as relevance, IFNULL  (parent_type, UUID()) as parent_type, IFNULL(parent_id, UUID()) as parent_id 
        FROM search_index
        WHERE MATCH(content) AGAINST('john*' IN BOOLEAN MODE) GROUP BY parent_type, parent_id ) as search 
    GROUP BY search.type, search.type_id) as count;
However, this makes me somewhat alarming. Here is the explanation for the count request:

enter image description here

search_indexis a full-text index on content. search_index_no_ftis an index for ALL columns except contentand id. There is a primary key on id.

, , ? , 2 ( ) 1?

+3
1

SQL_CALC_FOUND_ROWS? :

SELECT SQL_CALC_FOUND_ROWS some_fields FROM table WHERE xxx LIMIT 10;
SELECT FOUND_ROWS();

, . mysql docs: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows
, , .

0

More articles:


All Articles