Debian Question posted in
The oficial documentation can be found here.

MySQL Join table then Order By slow – Debian

I’m using this version of MySQL Ver 15.1 Distrib 10.5.4-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 and here is my server cpu info:

cat /proc/cpuinfo

enter image description here

I have 2 tables. Office and Customer. I have 1 row in my Office table and 1 Millions in Customer. You can see tables structure here : http://sqlfiddle.com/#!9/afa8f7

show create table customers;

customers | CREATE TABLE `customers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_office` int(11) unsigned NOT NULL,
  `code` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `firstname` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '    ',
  `familyname` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `phone` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `address` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `company_name` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL,
  `birthdate` date DEFAULT NULL,
  `archive` tinyint(1) DEFAULT 0,
  `id_company` int(11) NOT NULL,
  `revision` int(10) unsigned NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_office_2` (`id_office`,`code`),
  KEY `id_office` (`id_office`),
  KEY `id_company` (`id_company`),
  KEY `archive` (`archive`),
  KEY `firstname` (`firstname`),
  KEY `idx` (`id_office`,`firstname`),
  CONSTRAINT `fk_customers_company` FOREIGN KEY (`id_company`) REFERENCES `company` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_customers_offices` FOREIGN KEY (`id_office`) REFERENCES `offices` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1019063 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

show create table offices;

offices | CREATE TABLE `offices` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_company` int(11) NOT NULL,
  `code` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `description` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `archive` tinyint(1) NOT NULL DEFAULT 0,
  `prefix` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `contract_auto_number` tinyint(1) NOT NULL DEFAULT 0,
  `customer_auto_number` tinyint(1) NOT NULL DEFAULT 0,
  `email_synchro` tinyint(1) NOT NULL DEFAULT 1,
  `email_customer_synchro` tinyint(1) NOT NULL DEFAULT 1,
  `revision` smallint(5) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  KEY `id_company` (`id_company`),
  KEY `code_UNIQUE` (`code`) USING BTREE,
  KEY `description` (`description`),
  CONSTRAINT `fk_offices_company` FOREIGN KEY (`id_company`) REFERENCES `company` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

1st explain:

explain SELECT c.id, c.firstname FROM customers c ORDER BY c.firstname;
+------+-------------+-------+-------+---------------+-----------+---------+------+---------+-------------+
| id   | select_type | table | type  | possible_keys | key       | key_len | ref  | rows    | Extra       |
+------+-------------+-------+-------+---------------+-----------+---------+------+---------+-------------+
|    1 | SIMPLE      | c     | index | NULL          | firstname | 138     | NULL | 1017401 | Using index |
+------+-------------+-------+-------+---------------+-----------+---------+------+---------+-------------+

2nd explain:

explain SELECT c.id, c.firstname FROM customers c INNER JOIN offices o ON(c.id_office = o.id) ORDER BY c.firstname;
+------+-------------+-------+-------+---------------------------+------------+---------+-----------+--------+----------------------------------------------+
| id   | select_type | table | type  | possible_keys             | key        | key_len | ref       | rows   | Extra                                        |
+------+-------------+-------+-------+---------------------------+------------+---------+-----------+--------+----------------------------------------------+
|    1 | SIMPLE      | o     | index | PRIMARY                   | id_company | 4       | NULL      | 1      | Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | c     | ref   | id_office_2,id_office,idx | idx        | 4       | mced.o.id | 508700 | Using index                                  |
+------+-------------+-------+-------+---------------------------+------------+---------+-----------+--------+----------------------------------------------+

I do this query and it’s very fast (0.00003 seconds):

SELECT c.id, c.firstname
FROM customers c
ORDER BY c.firstname
LIMIT 25;

Now I do this query and it’s very slow (3 seconds)

SELECT c.id, c.firstname
FROM customers c INNER JOIN offices o ON(c.id_office = o.id)  
ORDER BY c.firstname
LIMIT 25;

There is a way to speed up the query ? Or the problem is my server CPU number ?

Tags:

3

Answers


  1. 0

    Given that you expect the customers table to be fairly large while the offices table to be fairly small, I can suggest indexing the former table for your join query:

    CREATE INDEX idx ON customers (id_office, firstname);  -- also includes id

    This index covers the join condition in your query, and it also includes firstname to cover the select clause. Note that InnoDB will automatically tag on the id primary key column to this index.

    Login or Signup to reply.
  2. 0

    Are those real queries? Or just fabricated to point out a flaw in the optimizer. (PS, this might be a good example to file as a bug report — bugs.mysql.com )

    The only thing that office is doing is "and this guy has an office". That can be achieved this way (perhaps much faster):

    SELECT c.id, c.firstname
    FROM customers c
    WHERE EXISTS( SELECT 1 FROM offices o
                    WHERE c.id_office = o.id )
    ORDER BY c.firstname
    LIMIT 25;

    What seems to be happening with your version… The Optimizer realizes that there is only one row in Office, so it decides to get that out of the way first. But then it fails to see other things like the ORDER BY...LIMIT.

    0.00003 seconds smells fishy, as if the Query cache was involved. When timing SELECTs, do SELECT SQL_NO_CACHE ...

    Login or Signup to reply.
  3. 0

    I think that the single-row-table optimization is overriding any clues that LIMIT might give. See if this kludge helps:

    • Add a bogus row to office.

    And add this 2-column "composite" index: (firstname, id_office)

    I’m hoping those will trick it into doing the ORDER BY and LIMIT before worrying about the size of office.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search