Mysql – Getting stuck with creating better index (Optimize Join)

join;MySQLoptimizationperformancequery-performance

Im using the below query and it took min 5s to max 11s. I tried to create single column and multi-column index. But still I can't able to increase it's performance.

Row count on each table:

  • tbl1 – 400000
  • tbl2 – 200000
  • tbl3 – 50

Table definition:

CREATE TABLE `tbl1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `modified` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `customer` varchar(155) DEFAULT NULL,
  `social` enum( 'Facebook','twitter') DEFAULT 'LI',
  `grade` char(1) DEFAULT NULL,
  `Monthly` int(11) DEFAULT NULL,
  `Off_comments` text,
  `createDate` datetime DEFAULT NULL
  );

create table tbl2 
(
id int  NOT NULL 
);

create table tbl3 (
id int, frnd_id int
);

Select Query:

SELECT    `T1`.*, 
          `T2`.`Card_No` 
FROM      `tbl1` `T1` 
LEFT JOIN `tbl2` `T2` 
ON        T1.id = T2.id 
LEFT JOIN `tbl3` 
ON        T3.id = T2.On_site_id
WHERE     T1.modified<=Subtime(Now(),'00:00:10') 
AND       T1.customer != 'bhuvi'
AND       T1.social !='Facebook' 
AND       T1.social != 'twitter' 
AND       T3.frnd_id= '4' 
AND       T1.grade IN ('A','B', 'O') 
AND       ( 
                    T1.Monthly IS NULL 
          OR        T1.Monthly = 1) 
AND       ( 
                    Off_comments IS NULL 
          OR        Off_comments = '' 
          OR        Off_comments="''") 
ORDER BY  `T1.createDate`;

Indexed columns:

  • tbl1 – id,modified,customer, social,frnd_id
  • tbl2 – id
  • tbl3 – id

Explain plan:

Filesort
+- TEMPORARY
   table          temporary(tbl3,T2,T1)
   +- JOIN
      +- Filter with WHERE
      |  +- Bookmark lookup
      |     +- Table
      |     |  table          T1
      |     |  possible_keys  fk_T_1_idx
      |     +- Index lookup
      |        key            T1->fk_T_1_idx
      |        possible_keys  fk_T_1_idx
      |        key_len        5
      |        ref            db.T2.id
      |        rows           1
      +- JOIN
         +- Index lookup
         |  key            T2->id
         |  possible_keys  PRIMARY, id
         |  key_len        5
         |  ref            db.t3.id
         |  rows           2537
         +- Index lookup
            key            tbl3->INX_frnd_id
            possible_keys  PRIMARY,INX_frnd_id
            key_len        5
            ref            const
            rows           16

Best Answer

First, let's clean up the data.

Is Monthly a true/false value? Then use 0/1 and put it in TINYINT NOT NULL. Or is it tri-state: Yes/No/unspecified?

If Off_comments is an optional string, then cleanse the data so that the empty string and '' are turned into NULL when it is stored. (Meanwhile, one or two UPDATEs can clean up the data.)

I recommend those because OR is hard to optimize, and this will probably eliminate such.

LEFT says that the 'right' table is optional. Yet AND t3... says that you must find a row. So change both LEFT JOINs to JOINs.

Once that is done, it may be efficient to check for T3.frnd_id= '4' first instead of last. Could it be that there aren't many rows with 4?

Indexes...

Assuming the above works, T3 needs INDEX(frnd_id, id), then T2 needs INDEX(On_site_id, id).

If the optimizer prefers to start with T1, first look for any = things in the WHERE. But, the only possibility is monthly, depending on the discussion above. Off_comments is not a candidate for indexing since it is TEXT.

I'll digress a moment. What is T3 about? It has only 50 rows? Perhaps it is "over-normalization"? If so, couldn't you simply put frnd_id into T2?

Grrr... Have you "simplified" the question? This seems invalid: social enum( 'Facebook','twitter') DEFAULT 'LI', since LI is not an option in the enum!

Grrr-squared... This is probably not what you wanted:

`T1.createDate`

Instead:

`T1`.`createDate`

Please fix those and any other mis-simplifications!

T1 may be able to use INDEX(createDate).

One more thing: 1:1 relationships (T1.id = T2.id) are usually a questionable design. Is id the PRIMARY KEY on both tables?

Related Question