root@db1133.eqiad.wmnet[mediabackups]> show create table files\G *************************** 1. row *************************** Table: files Create Table: CREATE TABLE `files` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `wiki` int(10) unsigned NOT NULL, `upload_name` varbinary(255) DEFAULT NULL, `swift_container` int(10) unsigned DEFAULT NULL, `swift_name` varbinary(270) DEFAULT NULL, `file_type` tinyint(3) unsigned DEFAULT NULL, `status` tinyint(3) unsigned DEFAULT NULL, `sha1` varbinary(40) DEFAULT NULL, `md5` varbinary(32) DEFAULT NULL, `size` int(10) unsigned DEFAULT NULL, `upload_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `archived_timestamp` timestamp NULL DEFAULT NULL, `deleted_timestamp` timestamp NULL DEFAULT NULL, `backup_status` tinyint(3) unsigned DEFAULT 1, PRIMARY KEY (`id`), <------------------------- !!!!!! KEY `sha1` (`sha1`), KEY `file_type` (`file_type`), KEY `status` (`status`), KEY `backup_status` (`backup_status`), KEY `wiki` (`wiki`), KEY `swift_container` (`swift_container`), KEY `upload_name` (`upload_name`,`status`), KEY `upload_timestamp` (`upload_timestamp`), CONSTRAINT `files_ibfk_1` FOREIGN KEY (`file_type`) REFERENCES `file_types` (`id`), CONSTRAINT `files_ibfk_2` FOREIGN KEY (`status`) REFERENCES `file_status` (`id`), CONSTRAINT `files_ibfk_3` FOREIGN KEY (`wiki`) REFERENCES `wikis` (`id`), CONSTRAINT `files_ibfk_4` FOREIGN KEY (`backup_status`) REFERENCES `backup_status` (`id`), CONSTRAINT `files_ibfk_5` FOREIGN KEY (`swift_container`) REFERENCES `swift_containers` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4549852 DEFAULT CHARSET=binary 1 row in set (0.001 sec) -- simple query, just denormalize the data so it is in "human" readable form root@db1133.eqiad.wmnet[mediabackups]> select wiki_name, upload_name, size, status_name, sha1, backup_status_name FROM files JOIN file_status ON file_status.id = files.status JOIN backup_status ON backup_status.id = files.backup_status JOIN wikis ON wikis.id = files.wiki ORDER BY files.id LIMIT 10; <---- waiting for many minutes ^CCtrl-C -- query killed. Continuing normally. ERROR 1317 (70100): Query execution was interrupted root@db1133.eqiad.wmnet[mediabackups]> EXPLAIN select wiki_name, upload_name, size, status_name, sha1, backup_status_name FROM files JOIN file_status ON file_status.id = files.status JOIN backup_status ON backup_status.id = files.backup_status JOIN wikis ON wikis.id = files.wiki ORDER BY files.id LIMIT 10\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: files type: ALL <--------------------------- WTF????? possible_keys: status,backup_status,wiki key: NULL key_len: NULL ref: NULL rows: 4549851 Extra: Using where; Using temporary; Using filesort <---- WTF? *************************** 2. row *************************** id: 1 select_type: SIMPLE table: wikis type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: mediabackups.files.wiki rows: 1 Extra: *************************** 3. row *************************** id: 1 select_type: SIMPLE table: backup_status type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 4 Extra: Using where; Using join buffer (flat, BNL join) *************************** 4. row *************************** id: 1 select_type: SIMPLE table: file_status type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 1 ref: mediabackups.files.status rows: 1 Extra: 4 rows in set (0.001 sec) root@db1133.eqiad.wmnet[mediabackups]> analyze table files; +--------------------+---------+----------+-----------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------------+---------+----------+-----------------------------------------+ | mediabackups.files | analyze | status | Engine-independent statistics collected | | mediabackups.files | analyze | status | OK | +--------------------+---------+----------+-----------------------------------------+ 2 rows in set (54.456 sec) root@db1133.eqiad.wmnet[mediabackups]> select wiki_name, upload_name, size, status_name, sha1, backup_status_name FROM files JOIN file_status ON file_status.id = files.status JOIN backup_status ON backup_status.id = files.backup_status JOIN wikis ON wikis.id = files.wiki ORDER BY files.id LIMIT 10; <---- waiting for many minutes ^CCtrl-C -- query killed. Continuing normally. ERROR 1317 (70100): Query execution was interrupted root@db1133.eqiad.wmnet[mediabackups]> select wiki_name, upload_name, size, status_name, sha1, backup_status_name FROM files JOIN file_status ON file_status.id = files.status JOIN backup_status ON backup_status.id = files.backup_status JOIN wikis ON wikis.id = files.wiki WHERE files.id BETWEEN 1 AND 10; -- note the only change at the end +-----------+---------------------------------------------------------+--------+----------- | wiki_name | upload_name | size | status_nam +-----------+---------------------------------------------------------+--------+----------- | enwiki | !!!_(Chk_Chk_Chk)_-_One_Girl_One_Boy_cover_art.jpg | 31850 | public | enwiki | !!!_-_!!!_album_cover.jpg | 43672 | public | enwiki | !!!_-_Wallop.png | 118745 | public | enwiki | !0_Trombones_Like_2_Pianos.jpg | 25319 | public | enwiki | !ClaudiaPascoal.png | 26203 | public | enwiki | !Haunu.ogg | 13450 | public | enwiki | !Hero_(album).jpg | 38664 | public | enwiki | !Women_Art_Revolution_(documentary_film)_poster_art.jpg | 15446 | public | enwiki | !_(The_Song_Formely_Known_As)_by_Regurgitator.png | 146525 | public | enwiki | ""Motor-Cycle"_LP_cover-Lotti_Golden.jpg | 17001 | public +-----------+---------------------------------------------------------+--------+----------- 10 rows in set (0.001 sec) :-(