0

There are much more complex queries running on these tables that are much faster but this one takes 90+ seconds to run.

What am I doing wrong here? How can this be improved?

SELECT 
            r.id as `ID`,  
            concat(r.fname, " ", r.lname) as `Applicant`,
            r.added `App Time`,
            concat(trim(r.city), ", ", r.state) as `City`, 
            coalesce(q.count, 0) as `Attempts`,
            coalesce(q.last_called, 0) as `Last Called`,
            null as `Removed`
        FROM myfreshp_crm.records r 
        left join (
            SELECT rid, count(rid) as count, max(called) as last_called
            from myfreshp_crm.cc_queue
            where status = 'called'
            group by rid
        ) q on q.rid = r.id 
        left join (
            select rid, max(time) as appt
            from myfreshp_crm.calendar 
            where event = 'Appointment' 
            group by rid
        ) a on a.rid = r.id
        left join (
            select rid, max(sent) as sent
            from myfreshp_crm.cc_queue
            group by rid
        ) c on c.rid = r.id
        where 
        r.id not in (select lead_id from asap_blacklist) 
        and coalesce(q.count, 0) < 4
        AND ( 
            c.sent > (UNIX_TIMESTAMP() - (60 * 60 * 24 * 28)) OR
            r.added > (UNIX_TIMESTAMP() - (60 * 60 * 24 * 28))
        )
        AND (
            (
                a.appt is not null
                and a.appt < UNIX_TIMESTAMP()
                and c.sent is not null
                and c.sent > a.appt
            ) OR (
                r.source = 'Online' 
                and a.appt is null
            )
        )

Explain extended.. is as follows:

+----+-------------+----------------+------+---------------+-------------+---------+-------------------+---------+----------+----------------------------------------------+
| id | select_type | table          | type | possible_keys | key         | key_len | ref               | rows    | filtered | Extra                                        |
+----+-------------+----------------+------+---------------+-------------+---------+-------------------+---------+----------+----------------------------------------------+
|  1 | PRIMARY     | r              | ALL  | added,source  | NULL        | NULL    | NULL              | 3436521 |   100.00 | Using where                                  |
|  1 | PRIMARY     | <derived2>     | ref  | <auto_key0>   | <auto_key0> | 4       | myfreshp_crm.r.id |      10 |   100.00 | Using where                                  |
|  1 | PRIMARY     | <derived3>     | ref  | <auto_key1>   | <auto_key1> | 4       | myfreshp_crm.r.id |      15 |   100.00 | Using where                                  |
|  1 | PRIMARY     | <derived4>     | ref  | <auto_key1>   | <auto_key1> | 4       | myfreshp_crm.r.id |      15 |   100.00 | Using where                                  |
|  5 | SUBQUERY    | asap_blacklist | ALL  | NULL          | NULL        | NULL    | NULL              |     287 |   100.00 | NULL                                         |
|  4 | DERIVED     | cc_queue       | ALL  | rid           | NULL        | NULL    | NULL              |   77090 |   100.00 | Using temporary; Using filesort              |
|  3 | DERIVED     | calendar       | ALL  | rid,event     | NULL        | NULL    | NULL              |  102750 |    97.15 | Using where; Using temporary; Using filesort |
|  2 | DERIVED     | cc_queue       | ALL  | rid,status    | NULL        | NULL    | NULL              |   77090 |    99.39 | Using where; Using temporary; Using filesort |
+----+-------------+----------------+------+---------------+-------------+---------+-------------------+---------+----------+----------------------------------------------+
8 rows in set, 1 warning (0.08 sec)

Show warnings; provides this:

+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `myfreshp_crm`.`r`.`id` AS `ID`,concat(`myfreshp_crm`.`r`.`fname`,' ',`myfreshp_crm`.`r`.`lname`) AS `Applicant`,`myfreshp_crm`.`r`.`added` AS `App Time`,concat(trim(`myfreshp_crm`.`r`.`city`),', ',`myfreshp_crm`.`r`.`state`) AS `City`,coalesce(`q`.`count`,0) AS `Attempts`,coalesce(`q`.`last_called`,0) AS `Last Called`,NULL AS `Removed` from `myfreshp_crm`.`records` `r` left join (/* select#2 */ select `myfreshp_crm`.`cc_queue`.`rid` AS `rid`,count(`myfreshp_crm`.`cc_queue`.`rid`) AS `count`,max(`myfreshp_crm`.`cc_queue`.`called`) AS `last_called` from `myfreshp_crm`.`cc_queue` where (`myfreshp_crm`.`cc_queue`.`status` = 'called') group by `myfreshp_crm`.`cc_queue`.`rid`) `q` on((`q`.`rid` = `myfreshp_crm`.`r`.`id`)) left join (/* select#3 */ select `myfreshp_crm`.`calendar`.`rid` AS `rid`,max(`myfreshp_crm`.`calendar`.`time`) AS `appt` from `myfreshp_crm`.`calendar` where (`myfreshp_crm`.`calendar`.`event` = 'Appointment') group by `myfreshp_crm`.`calendar`.`rid`) `a` on((`a`.`rid` = `myfreshp_crm`.`r`.`id`)) left join (/* select#4 */ select `myfreshp_crm`.`cc_queue`.`rid` AS `rid`,max(`myfreshp_crm`.`cc_queue`.`sent`) AS `sent` from `myfreshp_crm`.`cc_queue` group by `myfreshp_crm`.`cc_queue`.`rid`) `c` on((`c`.`rid` = `myfreshp_crm`.`r`.`id`)) where ((not(<in_optimizer>(`myfreshp_crm`.`r`.`id`,`myfreshp_crm`.`r`.`id` in ( <materialize> (/* select#5 */ select `myfreshp_crm`.`asap_blacklist`.`lead_id` from `myfreshp_crm`.`asap_blacklist` where 1 ), <primary_index_lookup>(`myfreshp_crm`.`r`.`id` in <temporary table> on <auto_key> where ((`myfreshp_crm`.`r`.`id` = `materialized-subquery`.`lead_id`))))))) and (coalesce(`q`.`count`,0) < 4) and ((`c`.`sent` > <cache>((unix_timestamp() - (((60 * 60) * 24) * 28)))) or (`myfreshp_crm`.`r`.`added` > <cache>((unix_timestamp() - (((60 * 60) * 24) * 28))))) and (((`a`.`appt` is not null) and (`a`.`appt` < <cache>(unix_timestamp())) and (`c`.`sent` is not null) and (`c`.`sent` > `a`.`appt`)) or ((`myfreshp_crm`.`r`.`source` = 'Online') and isnull(`a`.`appt`)))) |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

The records table:

CREATE TABLE `records` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `uid` int(20) NOT NULL,
  `cid` int(20) NOT NULL,
  `vid` int(8) NOT NULL,
  `added` int(25) NOT NULL,
  `fname` varchar(50) NOT NULL,
  `mname` varchar(50) NOT NULL,
  `lname` varchar(50) NOT NULL,
  `address` varchar(200) NOT NULL,
  `city` varchar(50) NOT NULL,
  `state` varchar(50) NOT NULL,
  `zip` int(5) NOT NULL,
  `phone1` varchar(16) NOT NULL,
  `phone2` varchar(16) NOT NULL,
  `mobilephone` varchar(16) NOT NULL,
  `email` varchar(100) NOT NULL,
  `status` enum('active','inactive','followup','responded','sold','dead') NOT NULL,
  `ssn` varchar(11) NOT NULL,
  `perm` enum('yes','no') NOT NULL DEFAULT 'no',
  `printed_label` int(30) NOT NULL,
  `printed_letter` int(30) NOT NULL,
  `dob` varchar(20) NOT NULL,
  `source` varchar(15) NOT NULL DEFAULT 'imported',
  `opt_out` enum('no','yes') NOT NULL DEFAULT 'no',
  `other_data` longtext NOT NULL,
  `sms_opt_in` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `cid` (`cid`),
  KEY `uid` (`uid`),
  KEY `vid` (`vid`),
  KEY `status` (`status`),
  KEY `uid_2` (`uid`),
  KEY `printed_label` (`printed_label`),
  KEY `fname` (`fname`),
  KEY `mname` (`mname`),
  KEY `lname` (`lname`),
  KEY `phone1` (`phone1`),
  KEY `phone2` (`phone2`),
  KEY `printed_letter` (`printed_letter`),
  KEY `address` (`address`),
  KEY `city` (`city`),
  KEY `state` (`state`),
  KEY `added` (`added`),
  KEY `source` (`source`),
  KEY `email` (`email`),
  KEY `zip` (`zip`),
  KEY `ssn` (`ssn`),
  KEY `dob` (`dob`)
) ENGINE=InnoDB AUTO_INCREMENT=8938455 DEFAULT CHARSET=latin1

The cc_queue table:

CREATE TABLE `cc_queue` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `rid` int(20) NOT NULL,
  `sent` int(30) NOT NULL,
  `called` int(30) NOT NULL,
  `reason` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
  `status` enum('waiting','called') COLLATE utf8_unicode_ci NOT NULL,
  `disposition` longtext COLLATE utf8_unicode_ci NOT NULL,
  `comments` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
  `sentToCC` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `rid` (`rid`),
  KEY `status` (`status`),
  KEY `sent` (`sent`),
  KEY `called` (`called`),
  KEY `sentToCC` (`sentToCC`)
) ENGINE=MyISAM AUTO_INCREMENT=77097 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

The calendar table:

CREATE TABLE `calendar` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL,
  `rid` int(20) NOT NULL,
  `added` int(25) NOT NULL,
  `time` int(11) NOT NULL,
  `event` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
  `details` varchar(1000) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `uid` (`uid`),
  KEY `rid` (`rid`),
  KEY `added` (`added`),
  KEY `time` (`time`),
  KEY `event` (`event`(333))
) ENGINE=MyISAM AUTO_INCREMENT=151930 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

The asap_blacklist table:

CREATE TABLE `asap_blacklist` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time` int(11) NOT NULL,
  `lead_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1483 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

And here's what information_schema says...

select * from information_schema.tables where table_name in ('records', 'cc_queue', 'calendar', 'asap_blacklist');
+---------------+--------------+----------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME     | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME         | UPDATE_TIME         | CHECK_TIME          | TABLE_COLLATION   | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------+----------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------------+
| def           | myfreshp_crm | asap_blacklist | BASE TABLE | InnoDB |      10 | Compact    |        287 |             57 |       16384 |               0 |            0 |         0 |           1483 | 2021-03-13 22:20:35 | NULL                | NULL                | utf8_bin          |     NULL |                |               |
| def           | myfreshp_crm | calendar       | BASE TABLE | MyISAM |      10 | Dynamic    |     102750 |            178 |    18325956 | 281474976710655 |      7480320 |         0 |         151930 | 2015-10-06 13:07:55 | 2021-05-04 21:38:09 | 2016-06-04 21:10:52 | utf8_unicode_ci   |     NULL |                |               |
| def           | myfreshp_crm | cc_queue       | BASE TABLE | MyISAM |      10 | Dynamic    |      77092 |            112 |    14584528 | 281474976710655 |      5064704 |   5935072 |          77097 | 2015-12-09 09:43:24 | 2021-05-05 09:30:02 | 2016-06-04 21:10:52 | utf8_unicode_ci   |     NULL |                |               |
| def           | myfreshp_crm | records        | BASE TABLE | InnoDB |      10 | Compact    |    3436523 |            204 |   702349312 |               0 |   1715929088 |   6291456 |        8938456 | 2021-02-18 04:16:51 | NULL                | NULL                | latin1_swedish_ci |     NULL |                |               |
+---------------+--------------+----------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------------+
4 rows in set (0.00 sec)
1
3

There are two things that jump out immediately with the information_schema Details:

  • MyISAM
  • differing collations

Regarding MyISAM

When there is a JOIN between MyISAM and InnoDB, the InnoDB tables will suffer from table-level lock behavior instead of row-level locking because of how MyISAM needs to operate.

The query engine in MySQL relies a great deal on index cardinality to determine an optimized plan. Index cardinality is generally stable in MyISAM tables so long as the data is static … which is rarely ever the case. InnoDB index cardinality is different from one minute to the next.

For the reasons above, I would strongly advise converting any MyISAM tables to InnoDB during your next database maintenance window.

Regarding the different collations

The query engine needs to convert utf8_bin, utf8_unicode_ci, and latin1_swedish_ci to something that is consistent with every query, and some of the indexes on your tables are quite large. This takes time. Ideally every table in the database has the same collation to avoid these expensive conversions.

Additional Reading

Here’s a bug report that’s been open for over a decade regarding MySQL’s performance when joining MyISAM and InnoDB tables for queries. It’s even worse when the result of the query is then used to INSERT or UPDATE a MyISAM table. Locking everywhere! 🤐

1

These are likely to help with performance (but I can't tell by how much):

cc_queue:  INDEX(status, rid, called)  -- (and toss INDEX(status))
calendar:  INDEX(event, rid, time)     -- see below
cc_queue:  INDEX(rid, sent)            -- (and toss INDEX(rid))

INDEX(event(333)) is rarely useful. If you have 5.7 or later, that "index prefix" is not necessary.

OR is a performance killer.

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.