CODEWITHSUNDEEP
phpmysqlapachequery-optimization
Pentium10
Pentium10

Reputation: 207893

Optimize MySQL count query, full info provided

I have this query that runs for over 3 minutes. I am looking to optimize to run in a few seconds:

SELECT Count(DISTINCT l.licitatii_id) c 
FROM   licitatii_ue l 
       INNER JOIN domenii_licitatii dl 
               ON l.licitatii_id = dl.licitatii_id 
                  AND dl.tip_licitatie = '2' 
       INNER JOIN domenii d 
               ON dl.domenii_id = d.domenii_id 
                  AND d.tip_domeniu = '1' 
                  AND d.status = 1 
WHERE  l.status = 1 
       AND l.data_limita >= '1357768800'

explain shows:

*** row 1 ***
          table:  d
           type:  ref
  possible_keys:  PRIMARY,key_status_tip_domeniu,ind_v1
            key:  key_status_tip_domeniu
        key_len:  9
            ref:  const,const
           rows:  39
          Extra:  Using where; Using index
*** row 2 ***
          table:  dl
           type:  ref
  possible_keys:  PRIMARY,licitatii_id,licitatii_id_2
            key:  PRIMARY
        key_len:  5
            ref:  web1db1.d.domenii_id,const
           rows:  1882
          Extra:  Using index
*** row 3 ***
          table:  l
           type:  eq_ref
  possible_keys:  PRIMARY,data_limita
            key:  PRIMARY
        key_len:  4
            ref:  web1db1.dl.licitatii_id
           rows:  1
          Extra:  Using where

Schema:

licitatii_ue

  PRIMARY KEY (`licitatii_id`),
  UNIQUE KEY `nr_ue` (`nr_ue`),
  KEY `nume` (`nume`),
  KEY `tip_licitatie` (`tip_licitatie`),
  KEY `surse_ue_id` (`surse_ue_id`),
  KEY `data_publicarii` (`data_publicarii`),
  KEY `tara_id` (`tara_id`),
  KEY `creat` (`creat`),
  KEY `data_limita` (`data_limita`),
  FULLTEXT KEY `nume_fulltext` (`nume`,`descriere`)
) ENGINE=MyISAM

/*Index Information*/
---------------------

Table         Non_unique  Key_name         Seq_in_index  Column_name      Collation  Cardinality  Sub_part  Packed  Null    Index_type  Comment  
------------  ----------  ---------------  ------------  ---------------  ---------  -----------  --------  ------  ------  ----------  ---------
licitatii_ue           0  PRIMARY                     1  licitatii_id     A               136456    (NULL)  (NULL)          BTREE                
licitatii_ue           0  nr_ue                       1  nr_ue            A               (NULL)    (NULL)  (NULL)  YES     BTREE                
licitatii_ue           1  nume                        1  nume             A                68228    (NULL)  (NULL)          BTREE                
licitatii_ue           1  tip_licitatie               1  tip_licitatie    A                   13    (NULL)  (NULL)  YES     BTREE                
licitatii_ue           1  surse_ue_id                 1  surse_ue_id      A                    1    (NULL)  (NULL)          BTREE                
licitatii_ue           1  data_publicarii             1  data_publicarii  A               136456    (NULL)  (NULL)  YES     BTREE                
licitatii_ue           1  tara_id                     1  tara_id          A                   41    (NULL)  (NULL)  YES     BTREE                
licitatii_ue           1  creat                       1  creat            A               136456    (NULL)  (NULL)  YES     BTREE                
licitatii_ue           1  data_limita                 1  data_limita      A                  770    (NULL)  (NULL)  YES     BTREE                
licitatii_ue           1  nume_fulltext               1  nume             (NULL)               1    (NULL)  (NULL)          FULLTEXT             
licitatii_ue           1  nume_fulltext               2  descriere        (NULL)               1    (NULL)  (NULL)  YES     FULLTEXT

domenii_licitatii

  PRIMARY KEY (`domenii_id`,`tip_licitatie`,`licitatii_id`),
  KEY `licitatii_id` (`licitatii_id`,`tip_licitatie`),
  KEY `licitatii_id_2` (`licitatii_id`)
) ENGINE=InnoDB 

/*Index Information*/
---------------------

Table              Non_unique  Key_name        Seq_in_index  Column_name    Collation  Cardinality  Sub_part  Packed  Null    Index_type  Comment  
-----------------  ----------  --------------  ------------  -------------  ---------  -----------  --------  ------  ------  ----------  ---------
domenii_licitatii           0  PRIMARY                    1  domenii_id     A                   20    (NULL)  (NULL)          BTREE                
domenii_licitatii           0  PRIMARY                    2  tip_licitatie  A                  228    (NULL)  (NULL)          BTREE                
domenii_licitatii           0  PRIMARY                    3  licitatii_id   A               430634    (NULL)  (NULL)          BTREE                
domenii_licitatii           1  licitatii_id               1  licitatii_id   A               430634    (NULL)  (NULL)          BTREE                
domenii_licitatii           1  licitatii_id               2  tip_licitatie  A               430634    (NULL)  (NULL)          BTREE                
domenii_licitatii           1  licitatii_id_2             1  licitatii_id   A               430634    (NULL)  (NULL)          BTREE

domenii

  PRIMARY KEY (`domenii_id`),
  KEY `key_status_tip_domeniu` (`status`,`tip_domeniu`),
  KEY `ind_v1` (`domenii_id`,`tip_domeniu`,`status`),
  KEY `parent_id` (`parent_id`)
) ENGINE=InnoDB 

/*Index Information*/
---------------------

Table    Non_unique  Key_name                Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null    Index_type  Comment  
-------  ----------  ----------------------  ------------  -----------  ---------  -----------  --------  ------  ------  ----------  ---------
domenii           0  PRIMARY                            1  domenii_id   A                   79    (NULL)  (NULL)          BTREE                
domenii           1  key_status_tip_domeniu             1  status       A                    4    (NULL)  (NULL)  YES     BTREE                
domenii           1  key_status_tip_domeniu             2  tip_domeniu  A                   11    (NULL)  (NULL)          BTREE                
domenii           1  ind_v1                             1  domenii_id   A                   79    (NULL)  (NULL)          BTREE                
domenii           1  ind_v1                             2  tip_domeniu  A                   79    (NULL)  (NULL)          BTREE                
domenii           1  ind_v1                             3  status       A                   79    (NULL)  (NULL)  YES     BTREE                
domenii           1  parent_id                          1  parent_id    A                   79    (NULL)  (NULL)          BTREE

Upvotes: 2

Views: 190

Answers (2)

DRapp
DRapp

Reputation: 48139

You really need compound indexes on your tables.

table             index
licitatii         ( status, data_limita, licitatii_id )
domenii_licitatii ( licitatii_id, tip_licitatie )
domenii           ( domenii_id, tip_domeniu, status )

You should have compound keys that help to optimize your joins AND where criteria. Additionally, as I have them included, the query engine can find the values for the joins directly from the indexes instead of going back to the pages of actual data. If you only have a single column in the index, the query would then go to the raw data to join. See what it does for you.

Ok, yes, the other two indexes and you added the third -- didn't help. Then I would try reversing the query some... Not exactly knowing the data, but it looks like they are some type of lookup category elements. Try

select STRAIGHT_JOIN
      Count(DISTINCT dl.licitatii_id) c
   from
      domenii d
         join domenii_licitatii dl
            on d.domenii_id = dl.domenii_id
           AND dl.tip_licitatie = '2'
            JOIN licitatii_ue L
               on dl.licitatii_id = L.licitatii_id
              AND L.status = 1
              AND L.Data_Limita >= '1357768800'
   where
          d.tip_domeniu = '1'
      and d.status = 1

added by Pentium10

Explain show the following:

+---+--------+----+--------+---------------------------------------+------------------------+---+----------------------------+------+--------------------------+
| 1 | SIMPLE | d  | ref    | PRIMARY,key_status_tip_domeniu,ind_v1 | key_status_tip_domeniu | 9 | const,const                |   39 | Using where; Using index |
| 1 | SIMPLE | dl | ref    | PRIMARY,licitatii_id,licitatii_id_2   | PRIMARY                | 5 | web1db1.d.domenii_id,const | 1882 | Using index              |
| 1 | SIMPLE | L  | eq_ref | PRIMARY,data_limita,i1                | PRIMARY                | 4 | web1db1.dl.licitatii_id    |    1 | Using where              |
+---+--------+----+--------+---------------------------------------+------------------------+---+----------------------------+------+--------------------------+

Upvotes: 1

Paul Stanley
Paul Stanley

Reputation: 4098

Here I have moved the WHERE clause at the end into a subquery at the beginning, so it doesn't have to filter more rows created by the INNER JOINs at the end, it is already filtered correctly in time for the the first INNER JOIN. This runs 2 queries, but it should be faster.

SELECT Count(DISTINCT l.licitatii_id) c 
FROM   (SELECT DISTINCT licitatii_id 
        FROM   licitatii_ue l 
        WHERE  l.status = 1 
        AND    l.data_limita >= '1357768800' 
        ) l 
       INNER JOIN domenii_licitatii dl 
               ON l.licitatii_id = dl.licitatii_id 
                  AND dl.tip_licitatie = '2' 
       INNER JOIN domenii d 
               ON dl.domenii_id = d.domenii_id 
                  AND d.tip_domeniu = '1' 
                  AND d.status = 1

Upvotes: 0

Related Questions