Query Optimization (WHERE, GROUP BY, LEFT JOINs)

group-by, left-join, mysql, optimization

Solution

I can see two opportunities for optimization right away:

Change an OUTER JOIN to INNER JOIN

Your query is currently scanning 434792 stories, and you should be able to narrow that down better, assuming not every story has a classification matching 'Home:Top%'. It would be better to use an index to find the classifications you're looking for, and then look up the matching stories.

But you're using `LEFT OUTER JOIN` for classifications, meaning all stories will be scanned whether they have a matching classification or not. Then you're defeating that by putting a condition on classifications in the `WHERE` clause, effectively making it mandatory that there be a classification matching your pattern with `LIKE`. So it's no longer an outer join, it's an inner join.

If you put the classifications table first, and make it an inner join, the optimizer will use that to narrow down the search for stories just to those that have a matching classification.

. . . 
FROM
  classifications
  INNER JOIN stories
    ON stories.id = classifications.story_id 
. . .

The optimizer is supposed to be able to figure out when it's advantageous to re-order tables, so you may not have to change the order in your query. But you do need to use an `INNER JOIN` in this case.

Add compound indexes

Your intersection tables image_story and author_story don't have compound indexes. It's often a big advantage to add compound indexes to the intersection tables in a many-to-many relationship, so that they can perform the join and get the "Using index" optimization.

ALTER TABLE image_story ADD UNIQUE KEY imst_st_im (story_id, image_id);
ALTER TABLE author_story ADD UNIQUE KEY aust_st_au (story_id, author_id);

Re your comments and update:

I'm not sure you created the new indexes correctly. Your dump of the indexes doesn't show the columns, and according to the updated EXPLAIN, the new indexes aren't being used, which I would expect to happen. Using the new indexes would result in "Using index" in the extra field of EXPLAIN, which should help performance.

Output of `SHOW CREATE TABLE` for each table would be more complete information than a dump of the indexes (without column names) as you have shown.

You may have to run ANALYZE TABLE once on each of those tables after creating the indexes. Also, run the query more than once, to make sure the indexes are in the buffer pool. Is this table InnoDB or MyISAM?

I also notice in your EXPLAIN output that the `rows` column shows a lot fewer rows being touched. That's an improvement.

Do you really need the `ORDER BY`? If you use `ORDER BY NULL` you should be able to get rid of the "Using filesort" and that may improve performance.

Re your update:

You still aren't getting the "Using index" optimization from your `image_story` and `author_story` tables. One suggestion I'd have is to eliminate the redundant indexes:

ALTER TABLE image_story DROP KEY story_id;
ALTER TABLE author_story DROP KEY story_id;

The reason is that any query that could benefit from the single-column index on story_id can also benefit from the two-column index on (story_id,image_id). Eliminating the redundant index helps the optimizer make a better decision (as well as saving some space). This is the theory behind a tool like pt-duplicate-key-checker.

I'd also check to make sure your buffer pool is large enough to hold your indexes. You don't want indexes to be paging in and out of the buffer pool during a query.

SHOW VARIABLES LIKE 'innodb_buffer_pool_size'

Check the size of indexes for your image_story table:

SELECT TABLE_NAME, INDEX_LENGTH FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME = 'image_story';

And compare that to how much of those indexes are currently residing in the buffer pool:

SELECT TABLE_NAME, GROUP_CONCAT(DISTINCT INDEX_NAME) AS INDEX_NAMES, SUM(DATA_SIZE) AS SIZE 
FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE_LRU 
WHERE TABLE_NAME = '`test`.`image_story`' AND INDEX_NAME <> 'PRIMARY'

Of course, change `test` above to the database name your table belongs to.

That information_schema table is new in MySQL 5.6. I assume you're using MySQL 5.6 because your EXPLAIN shows "Using index condition" which is also new in MySQL 5.6.

I don't use LONGTEXT at all unless I really need to story very long strings. Keep in mind:

- TEXT holds up to 64KB

- MEDIUMTEXT holds up to 16MB

- LONGTEXT holds up to 4GB

Problem

Original source