0

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
  • 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? – Jonathan Fite Jan 22 '18 at 16:58
  • This is the exact query. I'll post my table definition. – Bhuvanesh 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. – ypercubeᵀᴹ 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 – ypercubeᵀᴹ 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? – ypercubeᵀᴹ Jan 22 '18 at 17:38
1

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?

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.