Can the MySQL LEFT JOIN in this paginated SQL query be optimized? It is taking upwards of 7+ minutes

user_964491 0
Viewed 40

I have two tables. search has 89000 records and email_leads has 26000.

I would like to correlate which email and name has performed the search by left joining on the base_id column from the search table.

However when I include the LEFT JOIN in the query it takes upwards of 7+ minutes plus. When i remove it, the query executes instantly. Is there any way to restructure the query so that it doesn't take that long. The explain output makes me think mysql is actually joining all the rows it is scanning from the search table with the email_leads table instead of just the 10 records i am looking to return from the search table.

Here is the query i am running:

SELECT 
sh.base_id,
sh.client_id,
sh.app_id, 
sh.result_cnt, 
sh.search_type, 
sh.min_price, 
sh.max_price,
sh.bedrooms, 
sh.bathrooms, 
sh.neighborhoods, 
sh.office_connector, 
sh.created,
any_value(el.from_email) as email, 
any_value(el.from_name) as name
FROM search AS sh
LEFT JOIN email_leads AS el ON(sh.base_id = el.base_id)
WHERE date(sh.created) >= '2022-04-27'
AND date(sh.created) <= '2022-05-27'
GROUP BY sh.app_id, sh.base_id, sh.client_id, sh.result_cnt, sh.search_type, sh.min_price, sh.max_price,
sh.bedrooms, sh.bathrooms, sh.neighborhoods, sh.office_connector, sh.created ORDER BY sh.created DESC LIMIT 0, 10

Here are the indexes for both tables:

mysql> show index from search;
+--------+------------+------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+-------------------------+
| Table  | Non_unique | Key_name               | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression              |
+--------+------------+------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+-------------------------+
| search |          0 | PRIMARY                |            1 | id               | A         |       87659 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL                    |
| search |          1 | app_id_index           |            1 | app_id           | A         |           5 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL                    |
| search |          1 | search_type_index      |            1 | search_type      | A         |           3 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL                    |
| search |          1 | office_connector_index |            1 | office_connector | A         |           6 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL                    |
| search |          1 | bedrooms_index         |            1 | bedrooms         | A         |          53 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL                    |
| search |          1 | bathrooms_index        |            1 | bathrooms        | A         |           5 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL                    |
| search |          1 | min_price_index        |            1 | min_price        | A         |          33 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL                    |
| search |          1 | max_price_index        |            1 | max_price        | A         |          51 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL                    |
| search |          1 | base_id_index          |            1 | base_id          | A         |        5474 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL                    |
| search |          1 | client_id_index        |            1 | client_id        | A         |          18 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL                    |
| search |          1 | created_to_date        |            1 | NULL             | A         |          68 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | cast(`created` as date) |
+--------+------------+------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+-------------------------+
11 rows in set (0.02 sec)

mysql> show index from email_leads;
+-------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table       | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| email_leads |          0 | PRIMARY          |            1 | id          | A         |       21340 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| email_leads |          1 | from_email_index |            1 | from_email  | A         |        5211 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| email_leads |          1 | base_id_index    |            1 | base_id     | A         |        1482 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| email_leads |          1 | client_id_index  |            1 | client_id   | A         |           3 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.00 sec)

The explain output for the query:

+----+-------------+-------+------------+------+-----------------+---------------+---------+----------------------+-------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys   | key           | key_len | ref                  | rows  | filtered | Extra                                        |
+----+-------------+-------+------------+------+-----------------+---------------+---------+----------------------+-------+----------+----------------------------------------------+
|  1 | SIMPLE      | sh    | NULL       | ALL  | created_to_date | NULL          | NULL    | NULL                 | 87659 |    50.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | el    | NULL       | ref  | base_id_index   | base_id_index | 98      | srmanager.sh.base_id |    14 |   100.00 | NULL                                         |
+----+-------------+-------+------------+------+-----------------+---------------+---------+----------------------+-------+----------+----------------------------------------------+

Show create table output:

| search | CREATE TABLE `search` (
  `id` varchar(32) NOT NULL,
  `app_id` smallint NOT NULL,
  `client_id` varchar(32) NOT NULL,
  `base_id` varchar(32) NOT NULL,
  `result_cnt` int NOT NULL,
  `created` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `office_connector` varchar(255) DEFAULT 'all',
  `search_type` varchar(15) DEFAULT NULL,
  `bedrooms` varchar(300) DEFAULT 'all',
  `bathrooms` varchar(300) DEFAULT 'all',
  `neighborhoods` text,
  `min_price` int DEFAULT '0',
  `max_price` int DEFAULT '99999',
  PRIMARY KEY (`id`),
  KEY `app_id_index` (`app_id`),
  KEY `search_type_index` (`search_type`),
  KEY `office_connector_index` (`office_connector`),
  KEY `bedrooms_index` (`bedrooms`),
  KEY `bathrooms_index` (`bathrooms`),
  KEY `min_price_index` (`min_price`),
  KEY `max_price_index` (`max_price`),
  KEY `base_id_index` (`base_id`),
  KEY `client_id_index` (`client_id`),
  KEY `created_to_date` ((cast(`created` as date)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |

| email_leads | CREATE TABLE `email_leads` (
  `id` varchar(32) NOT NULL,
  `from_name` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `from_email` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `base_id` varchar(32) NOT NULL DEFAULT '',
  `client_id` varchar(32) NOT NULL DEFAULT '',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `from_email_index` (`from_email`),
  KEY `base_id_index` (`base_id`),
  KEY `client_id_index` (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
left-join join query-optimization mysql sql
3 Answers

You can give sub query a try instead of left join.

SELECT 
sh.base_id,
sh.client_id,
sh.app_id, 
sh.result_cnt, 
sh.search_type, 
sh.min_price, 
sh.max_price,
sh.bedrooms, 
sh.bathrooms, 
sh.neighborhoods, 
sh.office_connector, 
sh.created,
(select el.from_email from email_leads AS el where sh.base_id = el.base_id limit 1) as email, 
(select el.from_name from email_leads AS el where sh.base_id = el.base_id limit 1)  as name
FROM search AS sh
WHERE date(sh.created) >= '2022-04-27'
AND date(sh.created) <= '2022-05-27'
GROUP BY sh.app_id, sh.base_id, sh.client_id, sh.result_cnt, sh.search_type, sh.min_price, sh.max_price,
sh.bedrooms, sh.bathrooms, sh.neighborhoods, sh.office_connector, sh.created ORDER BY sh.created DESC LIMIT 0, 10
user_86516010

You use of DATE(sh.created) prevents using the index on that column when you put the column into a function. So it must do a table-scan of 89k rows. This is shown by type: ALL and rows: 87659 in the EXPLAIN.

Instead, try this:

...
WHERE sh.created >= '2022-04-27'
 sh.created < '2022-05-28'
...

Notice I made the latter condition strictly less-than (<) and advanced the date by one day. This is to allow the dates to match even if the datetime is up to 23:59:59.

Bill_Karwin_208600

Smells like the "explode-implode" symptom.

Problem 1: What you have gathers all the relevant columns from both tables, building a large temp table. Then is shrinks the results back based on the GROUP BY.

Problem 2: You only want 10 rows, yet it will gather all the rows (from both tables), then sort them, and finally deliver 10.

Problem 3: Non-sargable use of created

Let's try to turn the query inside-out. The "derived table" is optimized to get the 10 ids with minimal effort; then the rest gathers the other columns:

SELECT ...
    FROM (   -- "derived table"
        SELECT id
            FROM search
            WHERE created >= '2022-04-27'
              AND created  < '2022-04-27' + INTERVAL 1 MONTH
            GROUP BY id       -- probably this is all that is needed
            ORDER BY created DESC
            LIMIT 0, 10
         ) AS sh1
    JOIN sh ON sh.id = sh1.id   -- to get the rest of the columns
    LEFT JOIN email_leads AS el ON(sh.base_id = el.base_id)
ORDER BY sh.created DESC    -- yes, again

This will make the derived table fast:

INDEX(created, id)

Note that the JOIN and the LEFT JOIN will work with only 10 rows. The only other index needed is on e on el:

INDEX(base_id)
user_17668310
Related