Our site has a chronological event feed that uses MySQL. We have noticed that some of the queries tend to run for quite a bit longer than others. For example, some queries for our heaviest users take 250ms, while some the queries for users who don't have that many events take 30 seconds.
CREATE TABLE `events` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`enactor_id` int(11) unsigned NOT NULL,
`enactor_type` varchar(20) NOT NULL DEFAULT '',
`subject_id` int(11) unsigned NOT NULL,
`subject_type` varchar(20) NOT NULL DEFAULT '',
`result_id` int(11) unsigned NOT NULL,
`result_type` varchar(20) NOT NULL DEFAULT '',
`date_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`date_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `enactor_id` (`enactor_id`,`enactor_type`),
KEY `subject_id` (`subject_id`,`subject_type`),
KEY `result_id` (`result_id`,`result_type`),
KEY `date_created` (`date_created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Here is a query that showed up in our slow logs (it ran for 35 seconds):
SELECT `events`.`id`
FROM (`events`)
WHERE (
(
`subject_id` in (235799, 23987, 294828746, 234897224, 23429847) AND
`enactor_type` in ('User') AND
`subject_type` in ('Product') AND
`result_type` in ('Comment')
) OR (
`result_id` in (1503) AND
`enactor_type` in ('User') AND
`result_type` in ('Search')
) OR (
`subject_id` in (3523) AND
`subject_type` in ('User')
))
AND `events`.`date_created` <= '2012-12-13 19:44:48'
ORDER BY `events`.`id` desc
LIMIT 100;
Explain shows:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE events index subject_id,result_id,date_created PRIMARY 4 NULL 200 Using where
As a reference, this query completes in 70 ms:
SELECT `events`.`id`
FROM (`events`)
WHERE (
(
`subject_id` in (1234, 23876234, 234234, 232, 2342, 23424234, 2, 456456, 567, 56756756, 567567) AND
`enactor_type` in ('User') AND
`subject_type` in ('Product') AND
`result_type` in ('Comment')
) OR (
`enactor_id` in (879,486,11154) AND
`result_type` != "Search" AND
`result_type` != "Recommendation" AND
!((`result_type` = "Product" AND `subject_type` = "Store")) AND
`enactor_type` in ('User')
) OR (
`subject_id` in (29) AND
`enactor_type` in ('User') AND
`subject_type` in ('Store') AND
`result_type` in ('Product')
) OR (
`subject_id` in (285) AND
`subject_type` in ('User')
))
AND `events`.`date_created` <= '2012-12-13 22:12:47'
ORDER BY `events`.`id` desc
LIMIT 100;
The events table has ~30 million rows. The database server has 15GB of memory, and the events table is ~1GB. I have to imagine that some sort of indexing will cure this, but I have been playing around with different index choices to no avail.
Edited #1:
@Erwin Smout - You bring up a good point. For users without very many matching rows, the DB will need to go through all 30 million. That would explain the reason that heavy users have faster queries than new users or non-heavy users. The question, then, is how to speed up the worst case scenario, right?
@Pieter B, the indexes are listed in the create table definition above.
@ssmusoke, our site has a following model like many "social" sites. The events table is basically a chronological table of actions that have been taken on the site, and that query pulls events of different types that are relevant to the user based on who that user follows. I will look into changing the *_type's to tinyints to see what kind of performance gains that garners.
