[FIXED] MySQL server hangs after executing a simple query

Issue

I have a MySQL server running on ARM machine and I have a query which is run in phpmyadmin through web interface (or php on website)

select * from some_table where id IN (select id from other_table)

This simple query will result in MySQL service hanging until restart. What is the reason?

In other words:

if I manually enter id's - be it 1 or 100 - it will not fail:

select * from some_table where id IN (1,2,3,4,5,6,7,8,9....120)

on the other hand - if ids are generated from inner query, then service will hang:

select * from some_table where id IN (select id from other_table)

I have full access to server and it is possible to reconfigure it as necessary. How can I solve the issue?

Database server

Server: Localhost via UNIX socket

Server type: MySQL

Server version: 5.5.35-1ubuntu1 - (Ubuntu)

Protocol version: 10

User: root@localhost

Server charset: UTF-8 Unicode (utf8)

Web server

nginx/1.4.6

Database client version: libmysql - 5.5.35

PHP extension: mysqli

phpMyAdmin

Version information: 4.0.10deb1

P.S. what I mean by hanging is that mysql becomes unresponsive. Command top will show that MySQL service is eating up all cores. Restart done by command sudo /etc/init.d/mysql restart.

P.P.S. full query that hangs. fiddle: http://sqlfiddle.com/#!9/84fe8/1

select
    goo.t1_score,
    goo.t2_score,
    gr.t1_score,
    gr.t2_score,
    gr.unique_key
from games_ongoing goo, game_results gr
where
goo.id in (
        select max(id)
        from games_ongoing
        group by unique_key
        having count(id) >= 3 and MIN(t1_score) = MIN(t2_score) and MIN(t1_score) = 0
        )
and gr.unique_key = goo.unique_key

Solution

Answering your original question. In MySQL, this query:

select * from some_table where id IN (select id from other_table)

would execute the following query:

SELECT  id
FROM    other_table
WHERE   other_table.id = some_table.id

for each record in some_table, while this one:

select * from some_table where id IN (1,2,3,4,5,6,7,8,9....120)

will seek each value from the list in some_table. If there are lots of records on some_table but few in other_table, you should rewrite the query like this:

SELECT  st.*
FROM    (
        SELECT  DISTINCT
                id
        FROM    other_table
        ) ot
JOIN    some_table st
ON      st.id = ot.id

which will search each distinct other_table.id in the index on some_table.id.

Now, assuming there can be no negative score, you should run this query:

SELECT  *
FROM    game_results gr
JOIN    games_ongoing goo
ON      goo.id =
        (
        SELECT  id
        FROM    games_ongoing goi
        WHERE   goi.unique_key = gr.unique_key
                AND EXISTS
                (
                SELECT  NULL
                FROM    games_ongoing goz
                WHERE   goz.unique_key = goi.unique_key
                        AND (goz.t1_score, goz.t2_score) = (0, 0)
                )
                AND EXISTS
                (
                SELECT  NULL
                FROM    games_ongoing goc
                WHERE   goc.unique_key = goi.unique_key
                LIMIT   2, 1
                )
        ORDER BY
                goi.unique_key DESC, goi.id DESC
        LIMIT   1
        )

Create the following indexes for the query to work fast:

CREATE INDEX ix_game_results_unique_key ON game_results (unique_key);
CREATE INDEX ix_games_ongoing_unique_key ON games_ongoing (unique_key);
CREATE INDEX ix_games_ongoing_unique_key_t2_score_t2_score ON games_ongoing (unique_key, t1_score, t2_score);

See the fiddle: http://sqlfiddle.com/#!9/72444/1

Answered By - Quassnoi