Re: JOIN, LIMIT, ORDER performance problems
Here the results:
mysql> SELECT * FROM (SELECT `objects`.`key`,`objects`.`obj_id`,`u_ccc`.`ccc_identification` FROM `objects` LEFT JOIN `u_ccc` ON `u_ccc`.`table_link`='objects' AND `u_ccc`.`key_link`=`objects`.`key` WHERE 1 LIMIT 3000, 400 ) AS `sequence` ORDER BY `obj_id` ASC
400 rows in set (12.13 sec)
& & & & & & & & & & & & & & & & & & & & & & & & & & & & & &
mysql> EXPLAIN EXTENDED SELECT * FROM (SELECT `objects`.`key`,`objects`.`obj_id`,`u_ccc`.`ccc_identification` FROM `objects` LEFT JOIN `u_ccc` ON `u_ccc`
.`table_link`='objects' AND `u_ccc`.`key_link`=`objects`.`key` WHERE 1 LIMIT 3000, 400 ) AS `sequence` ORDER BY `o
bj_id` ASC;
+----+-------------+-----------------------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------------+------+---------------+------+---------+------+------+----------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 400 | Using filesort |
| 2 | DERIVED | objects | ALL | NULL | NULL | NULL | NULL | 4246 | |
| 2 | DERIVED | u_ccc | ALL | NULL | NULL | NULL | NULL | 4238 | |
+----+-------------+-----------------------+------+---------------+------+---------+------+------+----------------+
3 rows in set, 1 warning (9.98 sec)
mysql> SHOW WARNINGS; \g
+-------+------+------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------+
| Note | 1003 | select `sequence`.`key` AS `key`,`sequence`.`obj_id` AS `obj_id
`,`sequence`.`ccc_identification` AS `ccc_identification` from (select `usdb`.`objects`.`key` AS `key`,`usdb`.`objects`.`obj_id` AS
`obj_id`,`usdb`.`u_ccc`.`ccc_identification` AS `ccc_identificatio
n` from `usdb`.`objects` left join `usdb`.`u_ccc` on
(((`usdb`.`u_ccc`.`key_link` = `usdb`.`objects`.`key
`) and (`usdb`.`u_ccc`.`table_link` = _utf8'objects'
))) where 1 limit 3000,400) `sequence` order by `sequence`.`obj_id` |
+-------+------+------------------------------------------------------------------+
1 row in set (0.00 sec)
& & & & & & & & & & & & & & & & & & & & & & & & & & & & & &
mysql> show create table usdb.objects
'objects', 'CREATE TABLE `objects` (
`key` int(11) NOT NULL auto_increment,
`creator` varchar(100) collate utf8_unicode_ci NOT NULL,
`obj_id` varchar(20) collate utf8_unicode_ci NOT NULL,
`site` varchar(50) collate utf8_unicode_ci default NULL,
`complex` varchar(50) collate utf8_unicode_ci NOT NULL,
`trench` varchar(50) collate utf8_unicode_ci default NULL,
`square` varchar(50) collate utf8_unicode_ci default NULL,
`locus` varchar(50) collate utf8_unicode_ci default NULL,
`elevation_asl` decimal(5,2) default NULL,
`su` varchar(50) collate utf8_unicode_ci default NULL,
`field_no` varchar(50) collate utf8_unicode_ci default NULL,
`inv_no` varchar(50) collate utf8_unicode_ci default NULL,
`collection_date` date default NULL,
`prov_general` varchar(255) collate utf8_unicode_ci default NULL,
`prov_sector` varchar(255) collate utf8_unicode_ci default NULL,
`prov_specify_sector` varchar(255) collate utf8_unicode_ci default NULL,
`category` varchar(50) collate utf8_unicode_ci default NULL,
`class` varchar(50) collate utf8_unicode_ci default NULL,
`sub_class` varchar(50) collate utf8_unicode_ci NOT NULL,
`coin_specification` varchar(255) collate utf8_unicode_ci default NULL,
`identification_denomination` varchar(255) collate utf8_unicode_ci default NULL,
`vess_type_of_evidence` varchar(50) collate utf8_unicode_ci default NULL,
`vess_type_form` varchar(50) collate utf8_unicode_ci default NULL,
`vess_specification` varchar(100) collate utf8_unicode_ci default NULL,
`vess_extant_prop_rim` varchar(50) collate utf8_unicode_ci NOT NULL,
`vess_extant_prop_base` varchar(50) collate utf8_unicode_ci NOT NULL,
`vess_taxonomy` varchar(255) collate utf8_unicode_ci default NULL,
`vess_voids` varchar(255) collate utf8_unicode_ci default NULL,
`description` longtext collate utf8_unicode_ci,
`state_preservation` varchar(255) collate utf8_unicode_ci default NULL,
`preservation` varchar(255) collate utf8_unicode_ci default NULL,
`width` decimal(5,2) default NULL,
`thickness` decimal(5,2) default NULL,
`height` decimal(5,2) default NULL,
`length` decimal(5,2) default NULL,
`diameter` decimal(5,2) default NULL,
`weight` decimal(5,1) default NULL,
`additional_measures` varchar(255) collate utf8_unicode_ci default NULL,
`material_class` varchar(50) collate utf8_unicode_ci default NULL,
`material_type` varchar(255) collate utf8_unicode_ci default NULL,
`col_ext_recto` varchar(255) collate utf8_unicode_ci default NULL,
`decoration_ext` varchar(255) collate utf8_unicode_ci default NULL,
`surface_cond_ext` varchar(255) collate utf8_unicode_ci NOT NULL,
`alteration_ext_recto` varchar(255) collate utf8_unicode_ci default NULL,
`vess_coating_type_ext` varchar(255) collate utf8_unicode_ci default NULL,
`vess_coating_col_ext` varchar(255) collate utf8_unicode_ci default NULL,
`vess_treatment_type_ext` varchar(255) collate utf8_unicode_ci default NULL,
`coin_recto_description` longtext collate utf8_unicode_ci NOT NULL,
`col_int_verso` varchar(50) collate utf8_unicode_ci default NULL,
`decoration_int` varchar(255) collate utf8_unicode_ci default NULL,
`surface_cond_int` varchar(255) collate utf8_unicode_ci NOT NULL,
`alteration_int_verso` varchar(255) collate utf8_unicode_ci default NULL,
`vess_coating_type_int` varchar(255) collate utf8_unicode_ci default NULL,
`vess_coating_col_int` varchar(255) collate utf8_unicode_ci default NULL,
`vess_treatment_type_int` varchar(255) collate utf8_unicode_ci default NULL,
`coin_verso_description` longtext collate utf8_unicode_ci NOT NULL,
`vess_colour_core` varchar(50) collate utf8_unicode_ci default NULL,
`vess_inclusions` varchar(255) collate utf8_unicode_ci default NULL,
`firing` varchar(255) collate utf8_unicode_ci default NULL,
`working_technique` varchar(255) collate utf8_unicode_ci default NULL,
`artist_mint_name` varchar(255) collate utf8_unicode_ci default NULL,
`country_artist_mint` varchar(200) collate utf8_unicode_ci NOT NULL,
`artist_pen_name` varchar(255) collate utf8_unicode_ci default NULL,
`bibliography` longtext collate utf8_unicode_ci,
`comparative_evidences` longtext collate utf8_unicode_ci,
`drawings_present` varchar(5) collate utf8_unicode_ci NOT NULL,
`drawing_no` varchar(255) collate utf8_unicode_ci default NULL,
`photo_no` varchar(255) collate utf8_unicode_ci default NULL,
`notes` longtext collate utf8_unicode_ci NOT NULL,
`creation_date` date NOT NULL,
`modification_date` date NOT NULL,
`modification_author` varchar(50) collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (`key`)
) ENGINE=MyISAM AUTO_INCREMENT=4296 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci'
& & & & & & & & & & & & & & & & & & & & & & & & & & & & & &
mysql> show create table usdb.u_ccc
'u_ccc', 'CREATE TABLE `u_ccc` (
`key` int(5) NOT NULL auto_increment,
`table_link` varchar(255) collate utf8_unicode_ci default NULL,
`key_link` int(5) NOT NULL,
`ccc_affiliation_specific` varchar(255) collate utf8_unicode_ci NOT NULL,
`ccc_identification` varchar(255) collate utf8_unicode_ci NOT NULL,
`ccc_period` varchar(255) collate utf8_unicode_ci NOT NULL,
`ccc_relative cronology` varchar(255) collate utf8_unicode_ci NOT NULL,
`ccc_abs_range_cron_upper` int(10) NOT NULL,
`ccc_abs_range_crono_lower` int(10) NOT NULL,
`ccc_abs_punctual_crono` varchar(255) collate utf8_unicode_ci NOT NULL,
`ccc_phase` varchar(255) collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (`key`)
) ENGINE=MyISAM AUTO_INCREMENT=4257 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci'