[MySQL] 2 joins met zelfde tabel laat MySQL hangen

[Gamebuster]

Ik heb 2 queries die perfect draaien:

SQL:

SELECT users.id, 
    users.nickname AS nickname,
    users.created_at AS created_at,
    users.updated_at AS updated_at,
    users.roles AS roles,
    users.email AS email,
--    COUNT(posts.id) AS posts_count,
    COUNT(posts_last_week.id) AS posts_last_week_count
FROM users
-- LEFT OUTER JOIN posts AS posts ON posts.user_id = users.id
LEFT OUTER JOIN posts AS posts_last_week ON posts_last_week.user_id = users.id AND posts_last_week.created_at > NOW() - INTERVAL 1 WEEK
GROUP BY users.id

817 records in 0.9 sec

SQL:

SELECT users.id, 
    users.nickname AS nickname,
    users.created_at AS created_at,
    users.updated_at AS updated_at,
    users.roles AS roles,
    users.email AS email,
    COUNT(posts.id) AS posts_count-- ,
--    COUNT(posts_last_week.id) AS posts_last_week_count
FROM users
LEFT OUTER JOIN posts AS posts ON posts.user_id = users.id
-- LEFT OUTER JOIN posts AS posts_last_week ON posts_last_week.user_id = users.id AND posts_last_week.created_at > NOW() - INTERVAL 1 WEEK
GROUP BY users.id

817 records in 1.4 sec

Als ik een combinatie van deze 2 queries maak als volgt:

SQL:

SELECT users.id, 
    users.nickname AS nickname,
    users.created_at AS created_at,
    users.updated_at AS updated_at,
    users.roles AS roles,
    users.email AS email,
    COUNT(posts.id) AS posts_count,
    COUNT(posts_last_week.id) AS posts_last_week_count
FROM users
LEFT OUTER JOIN posts AS posts ON posts.user_id = users.id
LEFT OUTER JOIN posts AS posts_last_week ON posts_last_week.user_id = users.id AND posts_last_week.created_at > NOW() - INTERVAL 1 WEEK
GROUP BY users.id

...is MySQL "eindeloos" (na 2-3 minuten had ik het wel gezien) 100% CPU aan het trekken.

Waarom? Wie kan mij helpen?

[Creepy]

Wat zegt EXPLAIN precies? En wat doet mysql precies in die 2-3 minuten (bekijk hiervoor de mysql process list)

[Tuinstoel]

Dit oogt als een redelijk zware query waarvan ik me afvraag waarom er een join op de eigen tabel plaats vindt. Is de meuk wel geindexeerd? Dit neigt naar oneindig recursief door de posts join posts constructie. Ik vind het niet gek - afhankelijk van wat / hoeveel je in je tabellen hebt staan - dat dit enige tijd in beslag neemt. Wat wil je bereiken met deze query? Waarom moet het in één query?

[Gamebuster]

code:

mysql> EXPLAIN SELECT users.id,  
    ->     users.nickname AS nickname, 
    ->     users.created_at AS created_at, 
    ->     users.updated_at AS updated_at, 
    ->     users.roles AS roles, 
    ->     users.email AS email, 
    ->     COUNT(posts.id) AS posts_count, 
    ->     COUNT(posts_last_week.id) AS posts_last_week_count 
    -> FROM users 
    -> LEFT OUTER JOIN posts AS posts ON posts.user_id = users.id 
    -> LEFT OUTER JOIN posts AS posts_last_week ON posts_last_week.user_id = users.id AND posts_last_week.created_at > NOW() - INTERVAL 1 WEEK 
    -> GROUP BY users.id;
+----+-------------+-----------------+------+--------------------+---------+---------+--------------------+------+---------------------------------+
| id | select_type | table           | type | possible_keys      | key     | key_len | ref                | rows | Extra                           |
+----+-------------+-----------------+------+--------------------+---------+---------+--------------------+------+---------------------------------+
|  1 | SIMPLE      | users           | ALL  | NULL               | NULL    | NULL    | NULL               |  817 | Using temporary; Using filesort |
|  1 | SIMPLE      | posts           | ref  | user_id            | user_id | 5       | ychat-dev.users.id |   15 |                                 |
|  1 | SIMPLE      | posts_last_week | ref  | user_id,created_at | user_id | 5       | ychat-dev.users.id |   15 |                                 |
+----+-------------+-----------------+------+--------------------+---------+---------+--------------------+------+---------------------------------+
3 rows in set (0.00 sec)

Terwijl-ie bezig is met uitvoeren van query:

code:

mysql> SHOW PROCESSLIST;
+-----+------+-----------------+-----------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id  | User | Host            | db        | Command | Time | State                | Info                                                                                                 |
+-----+------+-----------------+-----------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
|  28 | root | localhost       | ychat-dev | Sleep   | 8008 |                      | NULL                                                                                                 |
|  29 | root | localhost       | ychat-dev | Sleep   | 3829 |                      | NULL                                                                                                 |
|  30 | root | localhost       | ychat-dev | Sleep   | 7770 |                      | NULL                                                                                                 |
|  31 | root | localhost:61027 | NULL      | Sleep   | 5927 |                      | NULL                                                                                                 |
|  32 | root | localhost:61028 | ychat-dev | Sleep   | 5581 |                      | NULL                                                                                                 |
|  33 | root | localhost:61029 | ychat-dev | Sleep   | 5640 |                      | NULL                                                                                                 |
|  34 | root | localhost:61077 | ychat-dev | Sleep   | 5497 |                      | NULL                                                                                                 |
|  35 | root | localhost:61078 | NULL      | Sleep   | 4085 |                      | NULL                                                                                                 |
|  52 | root | localhost:61365 | ychat-dev | Sleep   | 4084 |                      | NULL                                                                                                 |
|  54 | root | localhost:61652 | test      | Sleep   | 3273 |                      | NULL                                                                                                 |
|  55 | root | localhost:61654 | test      | Sleep   | 3517 |                      | NULL                                                                                                 |
|  56 | root | localhost:61669 | test      | Sleep   | 3444 |                      | NULL                                                                                                 |
|  57 | root | localhost:61675 | test      | Sleep   | 3330 |                      | NULL                                                                                                 |
|  59 | root | localhost:61677 | test      | Sleep   | 3371 |                      | NULL                                                                                                 |
|  61 | root | localhost:61686 | test      | Sleep   | 3317 |                      | NULL                                                                                                 |
|  63 | root | localhost:61688 | test      | Sleep   | 3291 |                      | NULL                                                                                                 |
|  69 | root | localhost:61701 | ychat-dev | Sleep   | 3229 |                      | NULL                                                                                                 |
|  71 | root | localhost:61703 | ychat-dev | Sleep   | 3207 |                      | NULL                                                                                                 |
|  74 | root | localhost:61714 | test      | Sleep   | 3126 |                      | NULL                                                                                                 |
|  76 | root | localhost:61717 | test      | Sleep   | 3116 |                      | NULL                                                                                                 |
|  79 | root | localhost:61721 | test      | Sleep   | 3099 |                      | NULL                                                                                                 |
|  81 | root | localhost:61724 | test      | Sleep   | 3068 |                      | NULL                                                                                                 |
|  82 | root | localhost:61725 | test      | Sleep   | 3063 |                      | NULL                                                                                                 |
|  84 | root | localhost:61729 | test      | Sleep   | 3056 |                      | NULL                                                                                                 |
|  86 | root | localhost:61731 | test      | Sleep   | 3054 |                      | NULL                                                                                                 |
|  93 | root | localhost:61739 | test      | Sleep   | 3017 |                      | NULL                                                                                                 |
|  95 | root | localhost:61743 | test      | Sleep   | 2982 |                      | NULL                                                                                                 |
|  96 | root | localhost:61770 | ychat-dev | Sleep   |  131 |                      | NULL                                                                                                 |
|  97 | root | localhost:61771 | NULL      | Sleep   |   56 |                      | NULL                                                                                                 |
|  99 | root | localhost:61774 | ychat-dev | Query   |   56 | Copying to tmp table | SELECT users.id,  
    users.nickname AS nickname, 
    users.created_at AS created_at, 
    users.u |
| 101 | root | localhost       | ychat-dev | Query   |    0 | NULL                 | SHOW PROCESSLIST                                                                                     |
+-----+------+-----------------+-----------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
31 rows in set (0.00 sec)

Tuinstoel schreef op maandag 07 mei 2012 @ 20:18:
Dit oogt als een redelijk zware query waarvan ik me afvraag waarom er een join op de eigen tabel plaats vindt.

Eigenlijk voer ik 2 verschillende joins op een andere tabel uit. Als ik deze 2 queries los doe, gaat het prima.

Is de meuk wel geindexeerd? Dit neigt naar oneindig recursief door de posts join posts constructie. Ik vind het niet gek - afhankelijk van wat / hoeveel je in je tabellen hebt staan - dat dit enige tijd in beslag neemt.

code:

mysql> SHOW INDEXES FROM posts
    -> ;
+-------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name        | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| posts |          0 | PRIMARY         |            1 | id              | A         |      247035 |     NULL | NULL   |      | BTREE      |         |               |
| posts |          1 | user_id         |            1 | user_id         | A         |       16469 |     NULL | NULL   | YES  | BTREE      |         |               |
| posts |          1 | topic_id        |            1 | topic_id        | A         |       12351 |     NULL | NULL   | YES  | BTREE      |         |               |
| posts |          1 | created_at      |            1 | created_at      | A         |      247035 |     NULL | NULL   |      | BTREE      |         |               |
| posts |          1 | origin_topic_id |            1 | origin_topic_id | A         |      247035 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)

Het gaat om ongeveer een kwart miljoen posts records.

Wat wil je bereiken met deze query? Waarom moet het in één query?

Ik wil alle gebruikers ophalen + het aantal posts van afgelopen week per gebruiker + het totaal aantal posts per gebruiker.

Het "moet" in 1 query omdat de daadwerkelijke query (die iets complexer/groter is) gegenereerd wordt door Ruby on Rails en er gesorteerd moet worden op beide kolommen (dus, kunnen sorteren op aantal posts in afgelopen week of aantal posts in totaal) op basis van een kolom-naam.

[ Voor 24% gewijzigd door Gamebuster op 07-05-2012 20:42 ]

[Verwijderd]

"Copying to tmp table" is altijd leuk, en inderdaad killing voor performance. Wat dat betreft klopt "Using temporary; Using filesort" ook, wat in principe betekent dat je je indexes moet fixen.
Kijk of je via een omweg niet toch iets kunt doen waarbij gebruik gemaakt kan worden van indexes.

[Knutselsmurf]

Omdat je alleen de gegevens van de gebruikers wilt weten, en wat aantallen posts per gebruiker, kun je met 1 enkele join toe.
Als je COUNT(post_id) as totaal, SUM(IF(post in gewenste periode,1,0)) as deze_week.
Deze constructie kun je meerdere keren in 1 query gebruiken, dus dan kun je in 1 query het totaal aantal posts, het aantal posts in het afgelopen jaar, de afgelopen maand en de afgelopen week uit de database toveren.

[Gamebuster]

Bedankt, die werkt prima

SQL:

SELECT users.id,  
    users.nickname AS nickname, 
    users.created_at AS created_at, 
    users.updated_at AS updated_at, 
    users.roles AS roles, 
    users.email AS email, 
    COUNT(posts.id) AS posts_count, 
    SUM(IF(posts.created_at > NOW() - INTERVAL 1 WEEK, 1, 0)) AS posts_last_week_count
FROM users 
LEFT JOIN posts ON posts.user_id = users.id
GROUP BY users.id