There are those 2 tables

  `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,
  `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.


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

  • An index on (performed_by, media_id) would help. But I fear that the two media_id columns - having different type/collation - will be a performance issue anyway. Commented Jul 21, 2015 at 21:21
  • 3
    (Irrelevant to the question) but you have 2 identical indexes on media (mid) and 2 indexes on media_resources_used (media_id). You only need one of each. Commented Jul 21, 2015 at 21:22
  • 1
    Make sure they have the same CHARSET. One has utf8 and the other latin1 (and take a backup of the table before you make any changes like that.) Commented Jul 21, 2015 at 21:53
  • 1
    And do you really have WHERE performed_by = NULL there? It should be WHERE performed_by IS NULL, otherwise no row will be updated. Commented Jul 21, 2015 at 21:57
  • 1
    @maddo7 yes, if fields types do not match, some type conversions have to take place, that may happen with charset/collation and it does the same as using a function on the column - direct index access is not possible anymore
    – jkavalik
    Commented Jul 22, 2015 at 11:06

1 Answer 1


Change the line

UNIQUE KEY media_id (user,media_id)


UNIQUE KEY media_id (media_id)

then execute the query it will perform better

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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