I have two tables, the first table contains all articles / blog posts within a CMS. Some of these articles may also appear in a magazine, in which case they have a foreign key relationship with another table that contains magazine specific information.

Here is a simplified version of the create table syntax for these two tables with some non-essential rows stripped out:

CREATE TABLE `base_article` (
  `date_published` datetime DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `description` text,
  `content` longtext,
  `is_published` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `base_article_date_published` (`date_published`),
  KEY `base_article_is_published` (`is_published`)

CREATE TABLE `mag_article` (
    `basearticle_ptr_id` int(11) NOT NULL,
    `issue_slug` varchar(8) DEFAULT NULL,
    `rubric` varchar(75) DEFAULT NULL,
    PRIMARY KEY (`basearticle_ptr_id`),
    KEY `mag_article_issue_slug` (`issue_slug`),
    CONSTRAINT `basearticle_ptr_id_refs_id` FOREIGN KEY (`basearticle_ptr_id`) REFERENCES `base_article` (`id`)

The CMS contains around 250,000 articles total and I have written a simple Python script that can be used to populate a test database with sample data if they want to replicate this issue locally.

If I select from one of these tables, MySQL has no problem picking an appropriate index or retrieving articles quickly. However, when the two tables are joined together in a simple query such as:

SELECT * FROM `base_article` 
INNER JOIN `mag_article` ON (`mag_article`.`basearticle_ptr_id` = `base_article`.`id`)
WHERE is_published = 1
ORDER BY `base_article`.`date_published` DESC

MySQL fails to pick an appropriate query and performance plummets. Here is the relevant explain extended (the execution time for which is over a second):

| id | select_type |    table     |  type  |           possible_keys           |   key   | key_len |                  ref                   | rows  | filtered |              Extra              |
|  1 | SIMPLE      | mag_article  | ALL    | PRIMARY                           | NULL    | NULL    | NULL                                   | 23830 | 100.00   | Using temporary; Using filesort |
|  1 | SIMPLE      | base_article | eq_ref | PRIMARY,base_article_is_published | PRIMARY | 4       | my_test.mag_article.basearticle_ptr_id |     1 | 100.00   | Using where                     |
  • EDIT SEPT 30: I can remove the WHERE clause from this query, but the EXPLAIN still looks the same and the query is still slow.

One potential solution is to force an index. Running the same query with FORCE INDEX (base_articel_date_published) results in a query that executes in around 1.6 milliseconds.

| id | select_type |    table     |  type  | possible_keys |             key             | key_len |           ref           | rows | filtered  |    Extra    |
|  1 | SIMPLE      | base_article | index  | NULL          | base_article_date_published |       9 | NULL                    |   30 | 833396.69 | Using where |
|  1 | SIMPLE      | mag_article  | eq_ref | PRIMARY       | PRIMARY                     |       4 | my_test.base_article.id |    1 | 100.00    |             |

I would prefer not to have to force an index on this query if I can avoid it, for several reasons. Most notably, this basic query can be filtered / modified in a variety of ways (such as filtering by the issue_slug) after which base_article_date_published may no longer be the best index to use.

Can anyone suggest a strategy for improving performance for this query?

  • if column "is_published" only keeps two or three values you could really drop that index KEY base_article_is_published (is_published).. looks to me it's an boolean type.. – Raymond Nijland Sep 30 '13 at 17:39
  • edited the answer – Raymond Nijland Nov 1 '13 at 0:23

What about this this should remove the need for an "Using temporary; Using filesort" because the data is in the right sort already.

You need to know the trick why MySQL needs "Using temporary; Using filesort" to remove that need.

See second sqlfriddle for an explain about removing the need

    FROM base_article

      (mag_article.basearticle_ptr_id = base_article.id)

      base_article.is_published = 1

      base_article.date_published DESC

see http://sqlfiddle.com/#!2/302710/2

Works pretty good i needed this also some time ago for Country / city tables see demo here with example data http://sqlfiddle.com/#!2/b34870/41

Edited you also may want to analyse this answer if base_article.is_published = 1 always returns 1 record like your explain explained an INNER JOIN deliverd table may give better performance like the queries in the answer below


| improve this answer | |
  • Life saving answer! I was using JOIN only but MySQL was not picking up index. Thanks so much Raymond – Maximus May 31 '14 at 1:51


(SELECT * FROM base_article
WHERE is_published = 1
ORDER BY date_published LIMIT 30) A
INNER JOIN mag_article B
ON A.id = B.basearticle_ptr_id;


(SELECT id FROM base_article
WHERE is_published = 1
ORDER BY date_published LIMIT 30) A
LEFT JOIN base_article ON A.id = B.id
LEFT JOIN mag_article C ON B.id = C.basearticle_ptr_id;


ALTER TABLE base_article DROP INDEX base_article_is_published;
ALTER TABLE base_article ADD INDEX ispub_datepub_index (is_published,date_published);


| improve this answer | |
  • Refactor: Doesn't work I'm afraid, because the LIMIT 30 is in the subquery (not all of those 30 rows will also be in the mag_articles table). If I move the LIMIT to the outer query the performance is the same as in my original. Modify Indexes: MySQL doesn't use that index either. Removing the WHERE clause from my original query doesn't seem to make a difference. – Joshmaker Sep 30 '13 at 13:53
  • The second refactor method worked incredibly good, the query time has been reduced dramatically from 8 seconds to 0.3 seconds in my table... thank you Sir!! – andreszs Mar 5 '18 at 13:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.