Getting stuck with creating better index (Optimize Join)

Question

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

• Jonathan Fite: 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?
• TheDataGuy: This is the exact query. I'll post my table definition.
• ypercubeᵀᴹ: OK. But you should really put the whole table definitions in the question. Including all columns and foreign keys.
• ypercubeᵀᴹ: 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ᵀᴹ: 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?

Answer 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?