MySQL Slow join - but not always and not on all tables

Question

We're experiencing a performance issue with a MySQL database that's so weird we need another set of eyes to tell us whether we're going crazy or not. We've got 2 MySQL Certified Developers in the team, but all they can say is : "this is impossible".

Anyway, here's the situation : we have a query that in theory should be reasonbly fast, but in reality is slow. If we slim down the query by removing 1 join, the query becomes extremely fast. If we remove a different join, it's still very slow, although the joined table has nearly the same structure. Worse even : the joins are SOMETIMES fast, sometimes not... it seems it's a random problem of some kind, although it has nothing to do with server load, since I have it on my local system too.

The table structure looks like this :

Table : article Rows : 57491
Field            Type                 Null   Key     Default     Extra
arti_id          int(10) unsigned     NO     PRI                 auto_increment
prev_id          int(10) unsigned     YES    MUL                 (null)
news_id          int(10) unsigned     NO     MUL                 (null)
cate_id          int(10) unsigned     NO     MUL                 (null)
pdf_id           int(10) unsigned     YES    MUL                 (null)
imag_id          int(10) unsigned     YES    MUL                 (null)
publication_date date                 NO     MUL                 (null)
title            varchar(255)         NO     MUL                 (null)
full_text        text                 YES    (null)              (null)

Table : category Rows : 3
Field            Type                 Null   Key     Default     Extra
cate_id          int(10) unsigned     NO     PRI                 auto_increment
code             varchar(7)           NO     (null)              (null)

Table : language Rows : 4
Field            Type                 Null     Key     Default     Extra
lang_id          int(10) unsigned     NO       PRI                 auto_increment
code             varchar(2)           NO       (null)              (null)

Table : newspaper Rows : 393
Field            Type                 Null     Key     Default     Extra
news_id          int(10) unsigned     NO       PRI                 auto_increment
lang_id          int(10) unsigned     NO       MUL                 (null)
name             varchar(255)         NO       UNI                 (null)

Now comes the weird part : as you can see 046_newspaper and 046_category both have a primary key (luckily). They're both referenced from a046_article by a foreign key. When we run the following query :

SELECT SQL_NO_CACHE
    article.*
FROM
    article
        INNER JOIN
        newspaper AS `n`
        ON
        article.news_id = n.news_id
ORDER BY
    article.publication_date DESC
LIMIT
    50

We get a result after 0.016 seconds, which is pretty fast.

Now when we replace the join with newspaper by a join with category :

SELECT SQL_NO_CACHE
    article.*
FROM
    article
        INNER JOIN
        category AS `c`
        ON
        article.cate_id = c.cate_id
ORDER BY
    article.publication_date DESC
LIMIT
    50

The query takes 1.02 seconds.

The odd thing is that this isn't always the case. Sometimes, for no apparent reason, the first query takes about that long too.

In the end what we want to do is :

SELECT SQL_CALC_FOUND_ROWS
    *,
    `n`.`name` AS `news_name`,
    `c`.`cate_id`,
    `c`.`code` AS `cate_name`,
    `l`.`code` AS `lang_name`
FROM
    `article`
        INNER JOIN
        `newspaper` AS `n`
        ON
        article.news_id = n.news_id
            INNER JOIN
            `category` AS `c`
            ON
            article.cate_id = c.cate_id
                INNER JOIN
                `language` AS `l`
                ON
                n.lang_id = l.lang_id
ORDER BY
    `article`.`publication_date` DESC
LIMIT
  50

which takes over 12 seconds at this point. This is partly due to the *, which we could replace by individual fields, but then it still takes 3 seconds.

We've tried a number of things : - Adding indexes (although all required indexes were there already and adding more is simply a bad idea) - Increasing the sort buffer size and key buffer - Looking at explain a lot... - Reading the MySQL manual over and over again - Reading a lot of forums However, nothing like this has solved the issue.

If anyone has any ideas, feel free to shout ! If you need the SQL-script or even access to the database, so you can give it a try, let me know... our client is complaining a lot about the slow pages...

Thanks !

Answer 1

1. Always use the EXPLAIN(QUERY) to profile and understand how MySQL is parsing your queries.
2. Check your INDEXes, MySQL is prolly selecting the wrong indexes for the select.

3. Try using SELECT and INDEX hints. http://dev.mysql.com/doc/refman/5.1/en/index-hints.html.

   SELECT * FROM table1 USE INDEX (col1_index,col2_index) WHERE col1=1 AND col2=2 AND col3=3;

   SELECT * FROM table1 IGNORE INDEX (col3_index) WHERE col1=1 AND col2=2 AND col3=3;

Answer 2

I would do a SHOW INDEX ON table for all of the tables and check that the cardinality column looks right (i.e. no NULLs). Or you can just do an ANALYZE on each table for good measure. Since you have 2 certified MySQL developers there, you probably did this already.

The next step is to look at the EXPLAIN and see how MySQL is optimizing it. You may need to use FORCE, USE or IGNORE to get it to optimize right. Speed will vary since MySQL and the OS with cache data (i.e. indexes), but not the query when you specify 'no cache'. Can you post the explain?

Answer 3

I think you can try with scalar query .