Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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
LIMIT 30

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?

share|improve this question
    
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

2 Answers 2

up vote 2 down vote accepted

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

SELECT
      *
    FROM base_article

    STRAIGHT_JOIN 
      mag_article
    ON
      (mag_article.basearticle_ptr_id = base_article.id)

    WHERE
      base_article.is_published = 1

    ORDER BY
      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

http://stackoverflow.com/questions/18738483/mysql-slow-query-using-filesort/18774937#18774937

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

REFACTOR THE QUERY

SELECT * FROM
(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;

or

SELECT B.*,C.* FROM
(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;

MODIFY YOUR INDEXES

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

GIVE IT A TRY !!!

share|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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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