Mysql Query performance very slow

Question

The below query was taking more than 8 min and 900 000 rows processed. it is very slow and affect my product. I can't identify why the query getting slow, all index are set fine.

explain SELECT 
    COUNT(DISTINCT (cinfo.CONTACT_ID))
FROM
    cinfo
        INNER JOIN
    LTocMapping ON cinfo.CONTACT_ID = LTocMapping.CONTACT_ID
WHERE
    (((((((((cinfo.COUNTRY LIKE '%Panama%')
        OR (cinfo.COUNTRY LIKE '%PANAMA%'))
        AND (((cinfo.CONTACT_EMAIL NOT LIKE '%test%')
        AND (cinfo.CONTACT_EMAIL NOT LIKE '%engine%'))
        OR (cinfo.CONTACT_EMAIL IS NULL)))
        AND ((SELECT 
            (GROUP_CONCAT(Temp.LIST_ID
                    ORDER BY Temp.LIST_ID) REGEXP ('.*,*221715000514445053,*.*$'))
            FROM
                LTocMapping Temp
            WHERE
                ((LTocMapping.CONTACT_ID = Temp.CONTACT_ID)
                    AND (((Temp.MAPPING_ID >= 221715000000000000)
                    AND (Temp.MAPPING_ID <= 221715999999999999))
                    OR ((Temp.MAPPING_ID >= 0)
                    AND (Temp.MAPPING_ID <= 999999999999))))
            GROUP BY Temp.CONTACT_ID) = '0'))
        AND ((SELECT 
            (GROUP_CONCAT(Temp.LIST_ID
                    ORDER BY Temp.LIST_ID) REGEXP ('.*,*221715000520574130,*.*$'))
            FROM
                LTocMapping Temp
            WHERE
                ((LTocMapping.CONTACT_ID = Temp.CONTACT_ID)
                    AND (((Temp.MAPPING_ID >= 221715000000000000)
                    AND (Temp.MAPPING_ID <= 221715999999999999))
                    OR ((Temp.MAPPING_ID >= 0)
                    AND (Temp.MAPPING_ID <= 999999999999))))
            GROUP BY Temp.CONTACT_ID) = '0'))
        AND (LTocMapping.LIST_ID IN (221715000520574130 , 221715000201569885)))
        AND (LTocMapping.STATUS = BINARY 'subscribed'))
        AND (((cinfo.CONTACT_STATUS = BINARY 'active')
        OR (cinfo.CONTACT_STATUS = BINARY 'softbounce'))
        AND (LTocMapping.STATUS = BINARY 'subscribed')))
        AND (((cinfo.CONTACT_ID >= 221715000000000000)
        AND (cinfo.CONTACT_ID <= 221715999999999999))
        OR ((cinfo.CONTACT_ID >= 0)
        AND (cinfo.CONTACT_ID <= 999999999999))))

And the answer will be

Below tables FYR

Table 1 :

mysql> desc cinfo;
+------------------------+--------------+------+-----+-----------+-------+
| Field                  | Type         | Null | Key | Default   | Extra |
+------------------------+--------------+------+-----+-----------+-------+
| CONTACT_ID             | bigint(19)   | NO   | PRI | NULL      |       |
| CONTACT_EMAIL          | varchar(100) | NO   | MUL | NULL      |       |
| TITLE                  | varchar(20)  | YES  |     | NULL      |       |
| FIRSTNAME              | varchar(100) | YES  |     | NULL      |       |
| LASTNAME               | varchar(50)  | YES  |     | NULL      |       |     |
| ADDED_BY               | varchar(20)  | YES  |     | NULL      |       |
| ADDED_TIME             | bigint(19)   | NO   |     | NULL      |       |
| LAST_UPDATED_TIME      | bigint(19)   | NO   |     | NULL      |       |
+------------------------+--------------+------+-----+-----------+-------+

Table 2 :

 mysql> desc LTocMapping;
+---------------------+--------------+------+-----+------------+-------+
| Field               | Type         | Null | Key | Default    | Extra |
+---------------------+--------------+------+-----+------------+-------+
| MAPPING_ID          | bigint(19)   | NO   | PRI | NULL       |       |
| CONTACT_ID          | bigint(19)   | NO   | MUL | NULL       |       |
| LIST_ID             | bigint(19)   | NO   | MUL | NULL       |       |
| STATUS              | varchar(100) | YES  |     | subscribed |       |
| MAPPING_STATUS      | varchar(20)  | YES  |     | connected  |       |
| MAPPING_TIME        | bigint(19)   | YES  |     | NULL       |       |
+---------------------+--------------+------+-----+------------+-------+

Answer 1

As Far as I can tell, your subqueries are the bottleneck:

• For the first subquery, you are using LTocMapping.CONTACT_ID
• For the second subquery, you are using LTocMapping.CONTACT_ID as well.

These references (to values of the outer query) are causing these inner queries to become correlated subqueries (also called dependent subqueries). And that means: For every row you are going to fetch on one of the outer tables (~970000) - you are firing 2 additional queries on another table.

So, that's 1.8 Million (as it seems as well not trivial) queries you are executing.

Most the time, a correlated subquery can be replaced by a proper join. But this depends on the usecase. You also can join the same table twice, when using a different alias.

But to outline some join-options, you need to explain, why the subqueries resulting in the condition group_concat(....) = '0' are important - or maybe better, what you want to achieve.

(ps.: You can also see, that explain outlines them as dependent subquery)

Answer 2

OR is inefficient, see if you can avoid it.

Leading wildcards in LIKE are inefficient. See if a FULLTEXT index would work for you.

With a proper COLLATION, you don't need to test both upper and lower case. Also you can avoid use of BINARY. In both cases, you might be able to use an index. (What indexes do you have?)

Try to change from

WHERE ( ( SELECT ... ) = '0' )

to

WHERE ( NOT EXISTS ( SELECT ... ) )

(The SELECT will need some modification.)

(Please get rid of some of the redundant parens; it is hard to read.)

(Please use SHOW CREATE TABLE; it is more descriptive than DESCRIBE.)