MySQL Forums
Forum List  »  Performance

Re: JOIN, LIMIT, ORDER performance problems
Posted by: Julian Bogdani
Date: March 10, 2010 01:41AM

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'

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: JOIN, LIMIT, ORDER performance problems
2360
March 10, 2010 01:41AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.