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:

  `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`.*, 
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:

   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
  • Could you please post the entire table definition as well as the current execution plan? Also, is this a truly representative query or do the number of arguments in the where clause change? Jan 22 '18 at 16:58
  • This is the exact query. I'll post my table definition.
    – TheDataGuy
    Jan 22 '18 at 17:00
  • 1
    OK. But you should really put the whole table definitions in the question. Including all columns and foreign keys. Jan 22 '18 at 17:22
  • 1
    You can at least post definitions that work. The ones you have will produce errors if someone tries to run an example. You can use dbfiddle.uk or sqlfiddle.com or rextester.com Jan 22 '18 at 17:34
  • 1
    Did you try the query by editing the LEFT JOIN tbl3 T3 ON T3.id = T2.id to INNER JOIN tbl3 T3 ON T3.id = T1.id? Jan 22 '18 at 17:38

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?


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:




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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.