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'm trying to run some reporting queries on a MySQL database, and I'm running into serious performance issues for some reason. One table, page_views, is extremely large (~70 million records), so some slowness is to be expected, but it's got lots of indexing, and all the queries are restricted to a certain date range on the started_at field. I'm seeing simple joins taking upwards of ten minutes. The query I've been tossing around lately (which I've stripped way down) looks like this:

select count(page_views.id) as views
from playpack_media
join page_views on page_views.itemId=playpack_media.media_Id
where playpack_media.playpack_id = 1
    and page_views.started_at BETWEEN '2014-06-23' and '2014-07-07'

It's taking about 11 minutes 30 seconds to run. DESCRIBE returns this analysis:

| id | select_type | table          | type | possible_keys        | key         | key_len | ref                                 | rows | Extra       |
+----+-------------+----------------+------+----------------------+-------------+---------+-------------------------------------+------+-------------+
|  1 | SIMPLE      | playpack_media | ref  | media_id,playpack_id | playpack_id | 4       | const                               |  594 |             |
|  1 | SIMPLE      | page_views     | ref  | itemId,started_at    | itemId      | 4       | db_playsmrt.playpack_media.media_id | 4236 | Using where |

It seems to bottleneck at the "copying to tmp table" stage. I've tried increasing tmp_table_size and max_heap_table_size to 256M and key_buffer_size to 64M - the latter change dropped the time from ~18 minutes to ~11, which is a big improvement but still terrible, and a further increase to 128M didn't do much.

One thing I noticed while hammmering at this is that page_views uses MyISAM and playpack_media uses InnoDB, which I'm reading can be an issue. However, changing page_views to InnoDB on my (much smaller) development server makes it dramatically slower. It's possible that it becomes worthwhile on larger tables, but just making the experiment would take a long time.

Does anyone have an idea what's going on here? What else should I be looking at?

share|improve this question
    
You say it has "lot's of indexes". But, does it specifically have an index on just page_views.itemId and playpack_media.media_Id? How about page_views.started_at? I'm not a MySQL guy, so I don't know how to interpret the DESCRIBE result. –  RThomas Jul 8 at 21:04
    
Yes, all three fields are indexed. There's no compound indexes on them, though, if that's what you had in mind. –  user1618143 Jul 8 at 21:10

2 Answers 2

up vote 2 down vote accepted

Look at the original query

select count(page_views.id) as views
from playpack_media
join page_views on page_views.itemId=playpack_media.media_Id
where playpack_media.playpack_id = 1
    and page_views.started_at BETWEEN '2014-06-23' and '2014-07-07'

You should do a couple of things.

First, create to compound indexes

ALTER TABLE page_views     ADD INDEX started_at_itemid_ndx    (started_at,itemid);
ALTER TABLE playpack_media ADD INDEX playpack_id_media_Id_ndx (playpack_id,media_Id)

Try to refactor the query

SELECT COUNT(1) as views FROM
(
    SELECT media_Id FROM playpack_media
    WHERE playpack_id = 1
) A INNER JOIN
(
    SELECT itemId FROM page_views WHERE
    started_at BETWEEN '2014-06-23' and '2014-07-07'
) B ON A.media_Id = B.itemId;

Perhaps breaking down the views by ItemID

SELECT IFNULL(A.media_id,'Total') Media,COUNT(1) as views FROM
(
    SELECT media_Id FROM playpack_media
    WHERE playpack_id = 1
) A INNER JOIN
(
    SELECT itemId FROM page_views WHERE
    started_at BETWEEN '2014-06-23' and '2014-07-07'
) B ON A.media_Id = B.itemId
GROUP BY A.media_id WITH ROLLUP;
share|improve this answer
    
(started_at, item_id) will be useless, as a range scan is used on the first column. –  jynus Jul 8 at 21:30
    
@jynus you are right in general but the rewrite suggested by Rolando will most probably use the index to create the derived table subresult. –  ypercube Jul 9 at 9:48
    
So, if I follow the logic here, you're using subqueries to force the database to crop out the unused part of each table separately using the indexes, and then it joins the derived tables without indexing? Or is there some kind of automatic indexing to speed that part up? –  user1618143 Jul 9 at 14:16
1  
Yes. The idea is to gather as little data as possible before doing the join. I learned this trick from this video : youtube.com/…. It does not solve every kind of JOIN problem. Try it out anyway and see if it works for you. –  RolandoMySQLDBA Jul 9 at 14:20
    
This is working remarkably well, which is kind of odd, because all the optimization advice I've ever seen about subqueries amounts to "use joins instead wherever possible". –  user1618143 Jul 9 at 18:10

Single-column indexes may not be helpful enough and it requires a temporary table. In fact, they may be harmful as non-optimal indexes are being used with little information on why it is happening.

The best possible indexes are

(playpack_media.playpack_id, playpack_media.media_id) AND (page_views.itemId, pageviews.started_at)

or

(page_views.started_at) AND (playpack_media.media_Id, playpack_media.playpack_id)

, depending on the selectivity of your conditions.

My bet is on the first option, as I will assume that the range on pageviews by itself selects a lot of records. Please make sure to get rid of unused indexes by this or other queries, as they will sometimes harm more than they help, by slowing down queries and confusing the query planner.

For future reference, I recommend combining the EXPLAIN command with FLUSH STATUS; <execute query>; SHOW SESSION STATUS like 'Hand%'; which will give you more detailed post-execution statistics.

share|improve this answer
    
Hmm. Unfortunately, the query I posted is just a concise example; the actual queries are much more complex. But I do see where you're going here, and I think I'll be able to apply it to the actual queries. I just wish the indexing wasn't going to shut things down for so long - last time I had to update page_views, the site was down for three hours while it reindexed. –  user1618143 Jul 8 at 21:36
    
You will love me :-) : percona.com/doc/percona-toolkit/2.2/… –  jynus Jul 8 at 21:37

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.