The following query took 6.6 seconds to run, and yielded 26 rows.
EXPLAIN result is two SIMPLE queries of type 'ref', using keys, scanning 23 and 48 rows.
Table f has 1000 rows and table m has 42000 rows.
seltype table type keys key keylen ref rows filtered extra SIMPLE f ref PRIMARY, forum_site_id 4 const 23 100.00 Using where; Using temporary; Using filesort forum_site_id, forums_flag_list_new_posts SIMPLE m ref forum_msg_forum_id, forum_msg_forum_id 5 locali_db.f.id 48 100.00 Using where forum_msg_status, forum_msg_date
Here is the query (quite a simple one):
SELECT
m.id AS msg_id,
m.public_id AS msg_public_id,
more fileds of this table ...
f.id AS forum_id,
f.public_id AS forum_public_id,
more fileds of this table ...
FROM
forum_msgs m
INNER JOIN forums f ON
m.forum_id = f.id
WHERE
f.site_id = 19
AND f.flag_list_new_posts = 1
AND m.msg_date >= 1434803744
AND m.status <> 11
ORDER BY
m.msg_date DESC
LIMIT
100
All fields in WHERE and ORDER BY clauses are of type INTEGER and are defined as INDEX. The field forum_id is defined as FOREIGN KEY.
I would be happy to find out what may be causing the outrageous performance :)
Aucun commentaire :
Enregistrer un commentaire