1

I have this very slow, simple query that joins a large table (~180M rows) with a smaller table (~60k rows) with a foreign key, filtering an indexed column on the smaller table, ordering by the primary key in the larger table, and then taking the 25 latest rows.

The EXPLAIN shows Using index; Using temporary; Using filesort on the smaller table. Why?

Engine: MySQL 5.7.

Query:

SELECT
    order.id,
    order.company_id,
    order.total
FROM
    order
INNER JOIN
    company ON company.id = order.company_id
WHERE
    company.company_headquarter_id = 23133
ORDER BY order.id DESC
LIMIT 25;
+----+-------------+------------+------------+------+---------------------------------------+----------------------------+---------+-----------------------+------+----------+----------------------------------------------+
| id | select_type | table      | partitions | type | possible_keys                         | key                        | key_len | ref                   | rows | filtered | Extra                                        |
+----+-------------+------------+------------+------+---------------------------------------+----------------------------+---------+-----------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | company    | NULL       | ref  | PRIMARY,company_headquarter_id_idx    | company_headquarter_id_idx | 8       | const                 |    6 |   100.00 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | order      | NULL       | ref  | company_id_idx                        | company_id_idx             | 8       | company.id            |  381 |   100.00 | NULL                                         |
+----+-------------+------------+------------+------+---------------------------------------+----------------------------+---------+-----------------------+------+----------+----------------------------------------------+
CREATE TABLE `order` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) NOT NULL,
  `total` double(18,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `company_id_idx` (`company_id`),
  CONSTRAINT `company_id_fk` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=186518644 DEFAULT CHARSET=latin1

CREATE TABLE `company` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `company_headquarter_id` bigint(20) NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `company_headquarter_id_idx` (`company_headquarter_id`),
  CONSTRAINT `company_headquarter_id_fk` FOREIGN KEY (`company_headquarter_id`) REFERENCES `company_headquarter` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=60825 DEFAULT CHARSET=latin1

CREATE TABLE `company_headquarter` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `phone` varchar(10) DEFAULT NULL,
  `address_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `address_id_idx` (`address_id`),
  CONSTRAINT `address_id_fk` FOREIGN KEY (`address_id`) REFERENCES `address` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=43862 DEFAULT CHARSET=latin1

CREATE TABLE `address` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `street_address` varchar(100) DEFAULT NULL,
  `zip` varchar(7) DEFAULT NULL,
  `state` varchar(2) DEFAULT NULL,
  `city` varchar(50) DEFAULT NULL,
  `country` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=147360955 DEFAULT CHARSET=latin1

The query becomes faster when I:

  • Remove the ORDER BY clause.
  • Filter company.company_headquarter_id with a company_headquarter_id that has a smaller number of orders. (company_headquarter_id = 23133 has ~3M rows in the order table)
  • Split it into two separate queries:

First:

SELECT
    company.id
FROM
    company
WHERE
    company.company_headquarter_id = 23133;

Second:

SELECT
    order.id,
    order.company_id,
    order.total
FROM
    order
WHERE
    order.company_id IN (20122, 50729, 50730, 50731, 50732, 50733)  /* From first query */
ORDER BY order.id DESC
LIMIT 25;

Any ideas?

Thank you.

EDIT:

When I do:

SELECT STRAIGHT_JOIN
    order.id,
    order.company_id,
    order.total
FROM
    order
INNER JOIN
    company ON company.id = order.company_id
WHERE
    company.company_headquarter_id = 23133
ORDER BY order.id DESC
LIMIT 25;

The query is much faster and EXPLAIN shows a temporary table is not created.

6
  • CONSTRAINT `company_headquarter_id_fk` FOREIGN KEY (`company_headquarter_id`) REFERENCES `company_headquarter` (`id`) - show DDL for company_headquarter table. Query: Must fail (non-quoted order tablename).
    – Akina
    Apr 23 at 17:56
  • @Akina Added to the post. Apr 23 at 18:20
  • Unless you have zillions of orders, any of these queries will be "fast enough". Are you experiencing "bad" performance?
    – Rick James
    Apr 23 at 22:10
  • Don't use DOUBLE for currency; use DECIMAL.
    – Rick James
    Apr 23 at 22:13
  • @RickJames Performance depends on how many orders company.company_headquarter_id has. For a company headquarter with 3M orders, it takes 12 seconds which is too slow. See edit. When I do STRAIGHT_JOIN, so that the order table comes first, the query is much faster. The MySQL docs says when your ORDER BY columns are different than the table used in the first join, a temporary table will be created. But I don't quite understand how going through 180M rows is faster than going through the smaller table first. Apr 23 at 23:31

1 Answer 1

0

If you were to upgrade to at least MySQL 8.0, you'd be able to take advantage of lateral joins:

create index order_ordered on order (company_id, id desc);

SELECT
    lateral_o.id,
    lateral_o.company_id,
    lateral_o.total
FROM
    company c
LATERAL (select o.id
               ,o.company_id
               ,o.total
         from   order o
         where  c.id = o.company_id
         order by o.company_id, o.id desc
         limit 25
        ) lateral_o
WHERE
    c.company_headquarter_id = 23133
ORDER BY lateral_o.id DESC
LIMIT 25;

This will do what it looks like, get the top 25 rows for each company_id value you find using an index, then do a final sort at the end.

The current plans you have will either be: going through the order table in reverse id order and checking if the company belongs to your filter until it succeeds 25 times. OR, it will be going to every single matching order row, sorting and returning the 25 desired. The first choice is fine if the chance of an order being one you care about is high, but awful if it's not. The second choice is great if there's only a few matching order rows, but will get worse when there is lots. My suggestion's performance will be related to how many company rows you identify, and it sounds like there won't often be many.

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.