Databases
StackOverflow2013
Postsdbo
POIs InnoDB sorting really THAT slow?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIs InnoDB sorting really THAT slow?
    primarykey
    data
    text
    <p>I had all my tables in myISAM but the table level locking was starting to kill me when I had long running update jobs. I converted my primary tables over to InnoDB and now many of my queries are taking over 1 minute to complete where they were nearly instantaneous on myISAM. They are usually stuck in the <code>Sorting result</code> step. Did I do something wrong?</p> <p>For example :</p> <pre><code>SELECT * FROM `metaward_achiever` INNER JOIN `metaward_alias` ON (`metaward_achiever`.`alias_id` = `metaward_alias`.`id`) WHERE `metaward_achiever`.`award_id` = 1507 ORDER BY `metaward_achiever`.`modified` DESC LIMIT 100 </code></pre> <p>Takes about 90 seconds now. Here is the describe :</p> <pre><code>+----+-------------+-------------------+--------+-------------------------------------------------------+----------------------------+---------+---------------------------------+-------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------------+--------+-------------------------------------------------------+----------------------------+---------+---------------------------------+-------+-----------------------------+ | 1 | SIMPLE | metaward_achiever | ref | metaward_achiever_award_id,metaward_achiever_alias_id | metaward_achiever_award_id | 4 | const | 66424 | Using where; Using filesort | | 1 | SIMPLE | metaward_alias | eq_ref | PRIMARY | PRIMARY | 4 | paul.metaward_achiever.alias_id | 1 | | +----+-------------+-------------------+--------+-------------------------------------------------------+----------------------------+---------+---------------------------------+-------+-----------------------------+ </code></pre> <p>It seems that now TONS of my queries get stuck in the "Sorting result" step :</p> <pre><code>mysql&gt; show processlist; +--------+------+-----------+------+---------+------+----------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +--------+------+-----------+------+---------+------+----------------+------------------------------------------------------------------------------------------------------+ | 460568 | paul | localhost | paul | Query | 0 | NULL | show processlist | | 460638 | paul | localhost | paul | Query | 0 | Sorting result | SELECT `metaward_achiever`.`id`, `metaward_achiever`.`modified`, `metaward_achiever`.`created`, `met | | 460710 | paul | localhost | paul | Query | 79 | Sending data | SELECT `metaward_achiever`.`id`, `metaward_achiever`.`modified`, `metaward_achiever`.`created`, `met | | 460722 | paul | localhost | paul | Query | 49 | Updating | UPDATE `metaward_alias` SET `modified` = '2009-09-15 12:43:50', `created` = '2009-08-24 11:55:24', ` | | 460732 | paul | localhost | paul | Query | 25 | Sorting result | SELECT `metaward_achiever`.`id`, `metaward_achiever`.`modified`, `metaward_achiever`.`created`, `met | +--------+------+-----------+------+---------+------+----------------+------------------------------------------------------------------------------------------------------+ 5 rows in set (0.00 sec) </code></pre> <p>Any why is that simple update stuck for 49 seconds? </p> <p>If it helps, here are the schemas : </p> <pre><code>| metaward_alias | CREATE TABLE `metaward_alias` ( `id` int(11) NOT NULL AUTO_INCREMENT, `modified` datetime NOT NULL, `created` datetime NOT NULL, `string_id` varchar(255) DEFAULT NULL, `shortname` varchar(100) NOT NULL, `remote_image` varchar(500) DEFAULT NULL, `image` varchar(100) NOT NULL, `user_id` int(11) DEFAULT NULL, `type_id` int(11) NOT NULL, `md5` varchar(32) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `string_id` (`string_id`), KEY `metaward_alias_user_id` (`user_id`), KEY `metaward_alias_type_id` (`type_id`) ) ENGINE=InnoDB AUTO_INCREMENT=858381 DEFAULT CHARSET=utf8 | | metaward_award | CREATE TABLE `metaward_award` ( `id` int(11) NOT NULL AUTO_INCREMENT, `modified` datetime NOT NULL, `created` datetime NOT NULL, `string_id` varchar(20) NOT NULL, `owner_id` int(11) NOT NULL, `name` varchar(100) NOT NULL, `description` longtext NOT NULL, `owner_points` int(11) NOT NULL, `url` varchar(500) NOT NULL, `remote_image` varchar(500) DEFAULT NULL, `image` varchar(100) NOT NULL, `parent_award_id` int(11) DEFAULT NULL, `slug` varchar(110) NOT NULL, `true_points` double DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `string_id` (`string_id`), KEY `metaward_award_owner_id` (`owner_id`), KEY `metaward_award_parent_award_id` (`parent_award_id`), KEY `metaward_award_slug` (`slug`), KEY `metaward_award_name` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=122176 DEFAULT CHARSET=utf8 | | metaward_achiever | CREATE TABLE `metaward_achiever` ( `id` int(11) NOT NULL AUTO_INCREMENT, `modified` datetime NOT NULL, `created` datetime NOT NULL, `award_id` int(11) NOT NULL, `alias_id` int(11) NOT NULL, `count` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `metaward_achiever_award_id` (`award_id`), KEY `metaward_achiever_alias_id` (`alias_id`) ) ENGINE=InnoDB AUTO_INCREMENT=77175366 DEFAULT CHARSET=utf8 | </code></pre> <p>And these in my my.cnf</p> <pre><code>innodb_file_per_table innodb_buffer_pool_size = 2048M innodb_additional_mem_pool_size = 16M innodb_flush_method=O_DIRECT </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USPaul Tarjan
    1. USPaul Tarjan
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POIs InnoDB sorting really THAT slow?
      1. USIndustrial
      1. VTFavorite
    2. VO
      singulars
      1. POIs InnoDB sorting really THAT slow?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POIs InnoDB sorting really THAT slow?
      1. USTomas Camin
      1. VTFavorite
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload