2

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?

| improve this question | | | | |
  • 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. – ypercubeᵀᴹ Jul 21 '15 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. – ypercubeᵀᴹ Jul 21 '15 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.) – ypercubeᵀᴹ Jul 21 '15 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. – ypercubeᵀᴹ Jul 21 '15 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 Jul 22 '15 at 11:06
-1

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

| improve this answer | | | | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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