Why does a multi-word Doctrine search cause MySQL to use up to 300% CPU?

Question

I have a table using Doctrine's Searchable behaviour, which works well for single word queries. However I've noticed that when a query includes a space, the page hangs and I can't get any response out of refreshing the page etc.

If I open Activity Monitor on my Mac, or SSH then 'top' in Ububntu, MySQL is running a process at up to 300% CPU. I have to kill the process in order to get things running normally again.

These are the SQL queries generated by Doctrine:

For a single word query:

SELECT COUNT(*) AS num_results FROM hotel h WHERE h.is_active = '1' 
AND h.id IN (SELECT id FROM hotel_index WHERE keyword = 'samui' GROUP 
BY id) 

For a multi-word query with a space in it:

SELECT COUNT(*) AS num_results FROM hotel h WHERE h.is_active = '1' 
AND h.id IN (SELECT id FROM hotel_index WHERE id IN (SELECT id FROM 
hotel_index WHERE keyword = 'sala') AND id IN (SELECT id FROM 
hotel_index WHERE keyword = 'samui') GROUP BY id) 

I've tried a few things like adding double quote marks around the 2 words.

Thanks

Answer 1

Is it possible to make Doctrine remove GROUP BY from the query?

It has no meaning and only degrades performance. This would perform much better:

SELECT  COUNT(*) AS num_results
FROM    hotel h
WHERE   h.is_active = '1' 
        AND h.id IN
        (
        SELECT id
        FROM   hotel_index
        WHERE  keyword = 'sala'
        )
        AND h.id IN
        (
        SELECT id
        FROM   hotel_index
        WHERE  keyword = 'samui'
        )

, provided that you have indexes on hotel(is_active) and hotel_index (keyword)

You could also rewrite it like this:

SELECT  COUNT(*) AS num_results
FROM    (
        SELECT  id
        FROM    hotel_index
        WHERE   keyword IN ('sala', 'samui')
        GROUP BY
                id
        HAVING COUNT(*) = 2
        ) q
JOIN    hotel h
ON      h.id = q.id
        AND h.is_active = '1' 

which would be yet more efficient.