MySQL Forums
Forum List  »  Optimizer & Parser

Re: Distinct/group by, order by and filesort
Posted by: Tolan Blundell
Date: February 25, 2010 11:54AM

Thanks for the response.

Answers to your notes:

* If id is UNIQUE, then no DISTINCT is needed. but it is not declared UNIQUE; why?
> There are multiple versions of each row in the table, and the set of versions
> for an object all share the same id. Id combined with TORM_REVISION_ID is
> unique though. Also doing a select where TORM_IS_CURRENT_REVISION=1 will
> return one instance of each id.

* There are a lot of redundant indexes. When one non-UNIQUE INDEX is a prefix of another, DROP the shorter one.
> Most are just test indexes where I'm trying to find an index that works for a
> particular query, so most aren't on the production server. Thanks for the tip
> though, that's useful to know.

* TORM_IS_CURRENT_REVISION seems to be NULLable -- perhaps a mistake?
> Yes, fixed now thanks. Actually it looks like there are loads of nullables
> that shouldn't be...

* I hope press_release has an index starting with (id).
> It does. I've given a new example query that better summarises my issues though
> as the one in my second post above joins two tables that have a 1:1
> relationship, but the reason I'm including the DISTINCT is that I'm joining
> onto an m:n link table (section_to_article). Example below.

SELECT DISTINCT article.* FROM article 

LEFT OUTER JOIN section_to_article ON ( article.id=section_to_article.article_id ) AND (section_to_article.section_id = 2)

WHERE 
    (article.published = 1) 
    AND ( article.TORM_IS_CURRENT_REVISION = 1 ) 


ORDER BY article.article_date DESC,article.id DESC 

LIMIT 50;



mk-visual-explain:



Filesort
+- TEMPORARY
   table          temporary(article,section_to_article)
   +- JOIN
      +- Distinct/Not-Exists
      |  +- Bookmark lookup
      |     +- Table
      |     |  table          section_to_article
      |     |  possible_keys  idx_section_to_art_art_id,idx_section_to_art_sec_id
      |     +- Index lookup
      |        key            section_to_article->idx_section_to_art_art_id
      |        possible_keys  idx_section_to_art_art_id,idx_section_to_art_sec_id
      |        key_len        5
      |        ref            my_db.article.id
      |        rows           1
      +- Filter with WHERE
         +- Bookmark lookup
            +- Table
            |  table          article
            |  possible_keys  article_published,art_is_current_rev,art_comp_x3,art_comp_x4,acx10,acx12,acx15
            +- Index lookup
               key            article->acx10
               possible_keys  article_published,art_is_current_rev,art_comp_x3,art_comp_x4,acx10,acx12,acx15
               key_len        3
               ref            const,const
               rows           7714



Table defs:

mysql> SHOW CREATE TABLE article\G
*************************** 1. row ***************************
       Table: article
Create Table: CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` enum('feature','interview','news') DEFAULT 'news',
  `headline` varchar(255) DEFAULT NULL,
  `article_date` int(11) DEFAULT NULL,
  `summary` varchar(255) DEFAULT NULL,
  `body` text,
  `thumbnail_asset_id` int(11) DEFAULT NULL,
  `user_submitted` tinyint(4) DEFAULT NULL,
  `article_end_date` int(11) DEFAULT NULL,
  `published` tinyint(1) DEFAULT NULL,
  `dirty` enum('dirty','staging_clean','live_clean') DEFAULT 'dirty',
  `author_id` int(11) DEFAULT NULL,
  `creation_date` int(11) DEFAULT NULL,
  `modification_date` int(11) DEFAULT NULL,
  `last_modified_by_user_id` int(11) DEFAULT NULL,
  `home_section_id` int(11) DEFAULT NULL,
  `root_template_id` int(11) DEFAULT NULL,
  `keywords` text,
  `byline` varchar(255) DEFAULT NULL,
  `TORM_CLASS` varchar(255) DEFAULT NULL,
  `friendly_path_title` varchar(255) DEFAULT NULL,
  `TORM_REVISION_ID` int(11) DEFAULT NULL,
  `TORM_IS_CURRENT_REVISION` tinyint(1) NOT NULL,
  `TORM_REVISION_LABEL` varchar(255) DEFAULT NULL,
  `TORM_REVISION_DATE` int(11) DEFAULT NULL,
  `TORM_REVISION_USER_ID` int(11) DEFAULT NULL,
  `locale` varchar(255) DEFAULT 'Default',
  KEY `torm_class_article` (`TORM_CLASS`),
  KEY `article_published` (`published`),
  KEY `article_date` (`article_date`),
  KEY `idx_article_dirty` (`dirty`),
  KEY `article_composite` (`id`,`TORM_REVISION_ID`),
  KEY `article_composite2` (`id`,`TORM_REVISION_ID`,`TORM_CLASS`,`TORM_IS_CURRENT_REVISION`,`published`),
  KEY `art_is_current_rev` (`TORM_IS_CURRENT_REVISION`),
  KEY `art_comp_x2` (`TORM_CLASS`,`TORM_IS_CURRENT_REVISION`,`article_date`,`id`),
  KEY `art_comp_x3` (`published`,`TORM_CLASS`,`TORM_IS_CURRENT_REVISION`,`article_date`,`id`),
  KEY `art_comp_x4` (`published`,`TORM_CLASS`,`TORM_IS_CURRENT_REVISION`,`article_date`,`id`),
  KEY `art_comp_x5` (`TORM_CLASS`,`TORM_IS_CURRENT_REVISION`,`article_date`,`id`),
  KEY `art_id` (`id`),
  KEY `art_comp_x6` (`id`,`published`,`TORM_CLASS`,`TORM_IS_CURRENT_REVISION`),
  KEY `art_comp_x7` (`TORM_CLASS`,`TORM_IS_CURRENT_REVISION`,`id`),
  KEY `acx9` (`article_date`,`id`,`published`,`TORM_IS_CURRENT_REVISION`),
  KEY `acx10` (`published`,`TORM_IS_CURRENT_REVISION`,`article_date`,`id`),
  KEY `acx11` (`TORM_REVISION_ID`,`published`,`TORM_IS_CURRENT_REVISION`,`article_date`,`id`),
  KEY `acx12` (`published`,`TORM_IS_CURRENT_REVISION`,`TORM_REVISION_ID`,`article_date`,`id`),
  KEY `acx13` (`id`,`published`,`TORM_IS_CURRENT_REVISION`,`TORM_REVISION_ID`,`article_date`),
  KEY `acx14` (`article_date`,`published`,`TORM_IS_CURRENT_REVISION`,`id`),
  KEY `acx15` (`published`,`TORM_IS_CURRENT_REVISION`,`article_date`),
  KEY `acx16` (`article_date`,`published`,`TORM_IS_CURRENT_REVISION`,`id`),
  FULLTEXT KEY `art_search_head` (`headline`),
  FULLTEXT KEY `art_search_summ` (`summary`),
  FULLTEXT KEY `art_search_body` (`body`)
) ENGINE=MyISAM AUTO_INCREMENT=537599 DEFAULT CHARSET=utf8


mysql> SHOW CREATE TABLE section_to_article\G
*************************** 1. row ***************************
       Table: section_to_article
Create Table: CREATE TABLE `section_to_article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `section_id` int(11) DEFAULT NULL,
  `article_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_section_to_art_art_id` (`article_id`),
  KEY `idx_section_to_art_sec_id` (`section_id`,`article_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10059 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

(idx_section_to_art_sec_id is badly named...)


Buffers / table info:


mysql> SHOW TABLE STATUS LIKE 'article'\G
*************************** 1. row ***************************
           Name: article
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 22356
 Avg_row_length: 2902
    Data_length: 64879816
Max_data_length: 281474976710655
   Index_length: 49170432
      Data_free: 0
 Auto_increment: 537599
    Create_time: 2010-02-25 18:19:53
    Update_time: 2010-02-25 18:19:55
     Check_time: 2010-02-25 18:20:29
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)


mysql> SHOW TABLE STATUS LIKE 'section_to_article'\G
*************************** 1. row ***************************
           Name: section_to_article
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 10812
 Avg_row_length: 146
    Data_length: 1589248
Max_data_length: 0
   Index_length: 475136
      Data_free: 27262976
 Auto_increment: 10059
    Create_time: 2010-02-23 15:12:16
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.04 sec)


mysql> SHOW VARIABLES LIKE '%buffer%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| bulk_insert_buffer_size | 8388608   | 
| innodb_buffer_pool_size | 67108864  | 
| innodb_log_buffer_size  | 1048576   | 
| join_buffer_size        | 131072    | 
| key_buffer_size         | 268435456 | 
| myisam_sort_buffer_size | 67108864  | 
| net_buffer_length       | 16384     | 
| preload_buffer_size     | 32768     | 
| read_buffer_size        | 2097152   | 
| read_rnd_buffer_size    | 8388608   | 
| sort_buffer_size        | 2097152   | 
| sql_buffer_result       | OFF       | 
+-------------------------+-----------+

I've just noticed that section_to_article is InnoDB actually, I'd forgotten about that. It was done that way because when it was MyISAM it kept getting corrupted. Article remains MyISAM because it's using fulltext indexing. I plan to move everything to InnoDB soon but first have to develop an alternate search indexing system to integrate with Solr.

Thanks for your help :)



Edited 1 time(s). Last edit at 02/25/2010 11:47AM by Tolan Blundell.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Distinct/group by, order by and filesort
2269
February 25, 2010 11:54AM


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.