lundi 29 juin 2015

optimizing a Slow MySQL query using EXPLAIN output

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