There are those 2 tables
CREATE TABLE IF NOT EXISTS `media` (
`mid` int(11) NOT NULL AUTO_INCREMENT,
`user` int(11) NOT NULL,
`order_type` int(11) NOT NULL,
`media_id` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`performed_by` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`flag` int(11) NOT NULL,
PRIMARY KEY (`mid`),
UNIQUE KEY `mid` (`mid`),
UNIQUE KEY `media_id` (`user`,`media_id`),
KEY `user` (`user`,`timestamp`),
KEY `performed_by` (`performed_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1687306 ;
CREATE TABLE IF NOT EXISTS `media_resources_used` (
`muid` int(11) NOT NULL AUTO_INCREMENT,
`media_id` varchar(50) NOT NULL,
`resources_used` text NOT NULL,
PRIMARY KEY (`muid`),
UNIQUE KEY `media_id` (`media_id`),
KEY `media_id_2` (`media_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8_unicode_ci AUTO_INCREMENT=1507815;
They're connected by media_id
. One row from media_resources_used can have n rows at media.
They both have over 1 million entries.
media_resources_used is used to determine what resources have been used on a media with a media_id and this works like a queue.
First I reserve a media row
UPDATE media SET performed_by = 'worker_id' WHERE performed_by = NULL LIMIT 1
Then I want to fetch the media row with the corresponding media_resources_used info
SELECT * FROM media AS me INNER JOIN media_resources_used AS au ON me.media_id = au.media_id WHERE me.performed_by = 'worker_id'
But this takes around 20 seconds to complete.
EXPLAIN shows
+----+-------------+-------+------+---------------+--------------+---------+-------+---------+-------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------------+---------+-------+---------+-------------------------------------------------+
| 1 | SIMPLE | me | ref | performed_by | performed_by | 63 | const | 1 | Using index condition |
| 1 | SIMPLE | au | ALL | NULL | NULL | NULL | NULL | 1439398 | Using where; Using join buffer (flat, BNL join) |
+----+-------------+-------+------+---------------+--------------+---------+-------+---------+-------------------------------------------------+
And it seems the index at media_id isn't even used.
When I run
SELECT * FROM media WHERE performed_by = 'worker_id'
SELECT * FROM media_resources_used WHERE media_id = '$media_id_from_above'
separated they both execute under 0.001 seconds.
What could be the issue there?
Best Answer
Change the line
UNIQUE KEY
media_id
(user
,media_id
)to
UNIQUE KEY
media_id
(media_id
)then execute the query it will perform better