1

i have a problem with a left join. Tables are myisam.

Tables

CREATE TABLE `article` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `force_startpage` tinyint(1) DEFAULT '0',
  `title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `text` longtext COLLATE utf8_unicode_ci,
  `text_length` int(11) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=MyISAM AUTO_INCREMENT=26767 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE `article_category` (
  `article_id` bigint(20) NOT NULL DEFAULT '0',
  `category_id` bigint(20) NOT NULL DEFAULT '0',
  `rank` bigint(20) DEFAULT '0',
  PRIMARY KEY (`article_id`,`category_id`),
  KEY `category_id` (`category_id`),
  KEY `article_id` (`article_id`),
  KEY `category_id_2` (`category_id`),
  KEY `article_id_2` (`article_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `category` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `slug` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `article_image` (
  `article_id` bigint(20) NOT NULL DEFAULT '0',
  `image_id` bigint(20) NOT NULL DEFAULT '0',
  `rank` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`article_id`,`image_id`),
  KEY `image_id` (`image_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `image` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `bu` varchar(400) COLLATE utf8_unicode_ci DEFAULT NULL,
  `copyright` varchar(400) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mimetype` varchar(4) COLLATE utf8_unicode_ci DEFAULT 'jpg',
  `width` int(11) DEFAULT NULL,
  `height` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=71644 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE article_image ADD FOREIGN KEY (image_id) REFERENCES image(id) ON DELETE CASCADE;
ALTER TABLE article_image ADD FOREIGN KEY (article_id) REFERENCES article(id) ON DELETE CASCADE;

ALTER TABLE article_category ADD FOREIGN KEY (category_id) REFERENCES category(id) ON DELETE CASCADE;
ALTER TABLE article_category ADD FOREIGN KEY (article_id) REFERENCES article(id) ON DELETE CASCADE;

My Query 1 Takes 0.05 ms:

SELECT 
  DISTINCT id, 
  force_startpage, 
  created_at 
FROM 
  (
    SELECT 
      a.id AS id, 
      a.force_startpage, 
      a.start_date,
      a.title, 
      a.text_length,
      a.created_at 
    FROM 
      article a
      LEFT JOIN article_category ac ON a.id = ac.article_id 
      LEFT JOIN category c ON c.id = ac.category_id
      LEFT JOIN article_image ai ON a.id = ai.article_id 
      LEFT JOIN image i ON i.id = ai.image_id 
    WHERE 
      a.text_length > 1 
      AND c.id = 1
    ORDER BY 
      a.force_startpage DESC, 
      a.created_at DESC
  ) dctrn_result 
ORDER BY 
  force_startpage DESC, 
  created_at DESC 
LIMIT 
  5

And the same Query with selecting the text field from article takes 15 Seconds

SELECT 
  DISTINCT id, 
  force_startpage, 
  created_at 
FROM 
  (
    SELECT 
      a.id AS id, 
      a.force_startpage, 
      a.start_date,
      a.title, 
      a.text
      a.text_length,
      a.created_at 
    FROM 
      article a
      LEFT JOIN article_category ac ON a.id = ac.article_id 
      LEFT JOIN category c ON c.id = ac.category_id
      LEFT JOIN article_image ai ON a.id = ai.article_id 
      LEFT JOIN image i ON i.id = ai.image_id 
    WHERE 
      a.text_length > 1 
      AND c.id = 1
    ORDER BY 
      a.force_startpage DESC, 
      a.created_at DESC
  ) dctrn_result 
ORDER BY 
  force_startpage DESC, 
  created_at DESC 
LIMIT 
  5

Table Article has ~20,000 rows

Table Category has 5 rows

Table Image has ~ 120,000 rows

I don't understand this huge time difference. If anyone could explain the problem and even better give hints for a fix?

Edit/ Explain results:

Query 1:

+----+-------------+------------+--------+-----------------------------------------------------------+---------------+---------+------------------------+-------+----------------------------------------------+
| id | select_type | table      | type   | possible_keys                                             | key           | key_len | ref                    | rows  | Extra                                        |
+----+-------------+------------+--------+-----------------------------------------------------------+---------------+---------+------------------------+-------+----------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL                                                      | NULL          | NULL    | NULL                   | 38504 | Using temporary; Using filesort              |
|  2 | DERIVED     | c          | const  | PRIMARY                                                   | PRIMARY       | 8       |                        |     1 | Using index; Using temporary; Using filesort |
|  2 | DERIVED     | ac         | ref    | PRIMARY,category_id,article_id,category_id_2,article_id_2 | category_id_2 | 8       |                        |  9239 | Using where                                  |
|  2 | DERIVED     | a          | eq_ref | PRIMARY                                                   | PRIMARY       | 8       | kinozeit.ac.article_id |     1 | Using where                                  |
|  2 | DERIVED     | ai         | ref    | PRIMARY                                                   | PRIMARY       | 8       | kinozeit.a.id          |     4 | Using index                                  |
|  2 | DERIVED     | i          | eq_ref | PRIMARY                                                   | PRIMARY       | 8       | kinozeit.ai.image_id   |     1 | Using index                                  |
+----+-------------+------------+--------+-----------------------------------------------------------+---------------+---------+------------------------+-------+----------------------------------------------+
6 rows in set (0.31 sec)

Query 2:

+----+-------------+------------+--------+-----------------------------------------------------------+---------------+---------+------------------------+-------+----------------------------------------------+
| id | select_type | table      | type   | possible_keys                                             | key           | key_len | ref                    | rows  | Extra                                        |
+----+-------------+------------+--------+-----------------------------------------------------------+---------------+---------+------------------------+-------+----------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL                                                      | NULL          | NULL    | NULL                   | 38504 | Using temporary; Using filesort              |
|  2 | DERIVED     | c          | const  | PRIMARY                                                   | PRIMARY       | 8       |                        |     1 | Using index; Using temporary; Using filesort |
|  2 | DERIVED     | ac         | ref    | PRIMARY,category_id,article_id,category_id_2,article_id_2 | category_id_2 | 8       |                        |  9239 | Using where                                  |
|  2 | DERIVED     | a          | eq_ref | PRIMARY                                                   | PRIMARY       | 8       | kinozeit.ac.article_id |     1 | Using where                                  |
|  2 | DERIVED     | ai         | ref    | PRIMARY                                                   | PRIMARY       | 8       | kinozeit.a.id          |     4 | Using index                                  |
|  2 | DERIVED     | i          | eq_ref | PRIMARY                                                   | PRIMARY       | 8       | kinozeit.ai.image_id   |     1 | Using index                                  |
+----+-------------+------------+--------+-----------------------------------------------------------+---------------+---------+------------------------+-------+----------------------------------------------+
6 rows in set (12.20 sec)

Edit 2 / Show Profiles (Due to comment from tombom)

+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000019 |
| Waiting for query cache lock   | 0.000005 |
| checking query cache for query | 0.000129 |
| checking permissions           | 0.000007 |
| checking permissions           | 0.000004 |
| checking permissions           | 0.000004 |
| checking permissions           | 0.000004 |
| checking permissions           | 0.000006 |
| Opening tables                 | 0.000036 |
| System lock                    | 0.000373 |
| optimizing                     | 0.000030 |
| statistics                     | 0.000095 |
| preparing                      | 0.000034 |
| Creating tmp table             | 0.000230 |
| executing                      | 0.000006 |
| Copying to tmp table           | 0.298139 |
| Sorting result                 | 0.049349 |
| Sending data                   | 0.281068 |
| removing tmp table             | 1.380771 |
| Sending data                   | 0.000013 |
| Waiting for query cache lock   | 0.000003 |
| Sending data                   | 0.000019 |
| init                           | 0.000017 |
| optimizing                     | 0.000004 |
| statistics                     | 0.000007 |
| preparing                      | 0.000007 |
| Creating tmp table             | 0.000022 |
| executing                      | 0.000002 |
| Copying to tmp table           | 0.065963 |
| Sorting result                 | 0.000718 |
| Sending data                   | 0.000015 |
| end                            | 0.000002 |
| removing tmp table             | 0.000005 |
| end                            | 0.000003 |
| query end                      | 0.000002 |
| closing tables                 | 0.000002 |
| removing tmp table             | 0.205378 |
| closing tables                 | 0.000018 |
| freeing items                  | 0.000012 |
| Waiting for query cache lock   | 0.000014 |
| freeing items                  | 0.000012 |
| Waiting for query cache lock   | 0.000002 |
| freeing items                  | 0.000001 |
| storing result in query cache  | 0.000002 |
| logging slow query             | 0.000002 |
| cleaning up                    | 0.000003 |
+--------------------------------+----------+

I'ts a bit faster now, because of query cache and -repair tables. But problem still exists.

Improve this question
5
  • Please put the keyword EXPLAIN in front of your queries and post the results.
    – tombom
    Commented Jul 24, 2014 at 11:26
  • @tombom I have edited the questions and added the explain results.
    – Evenbit GmbH
    Commented Jul 24, 2014 at 11:57
  • Absolutely the same, okay. Please have a look at this post and follow the instructions for the longer query: stackoverflow.com/a/11275107/447489 Then we can see with what the query is spending its time. As of now, I'd suspect, that it simply takes time to transport the query result over the network.
    – tombom
    Commented Jul 24, 2014 at 12:15
  • @tombom Thx for your work. I have posted the profile result in the edit.
    – Evenbit GmbH
    Commented Jul 24, 2014 at 12:55
  • @rechengehirn when profiling, use SQL_NO_CACHE to avoid query cache interferences.
    – jynus
    Commented Jul 24, 2014 at 13:26

1 Answer 1

Reset to default
2

You have 2 temporary tables being created. It is highly likely that the big difference comes from the fact that MEMORY, the engine used by MySQL to create temporary tables in, well, memory does not support TEXT/BLOB data types (independently of max_heap_table_size), so they are forced to be created on disk, having a great slow down .

Profiling your query as suggested by @tombon or monitoring show session status like '%tmp%'; and show session status like 'Hand%'; before and after executing both queries will probably expose this problem.

Improve this answer
2
  • Hi jymus. Thats right (see edited post with profile). But what to do?
    – Evenbit GmbH
    Commented Jul 24, 2014 at 12:57
  • That depends on what you want to achieve. The query shown is probably not your final stop, because otherwise you would just use the first version. So, either you change your query to avoid the temporary tables (the outer query creates the temporary due to the DISTINCT + ORDER BY), or you break it out in different steps (a stored procedure may help), or you try to speed up the temporary tables on disk using ramdisk if you have the memory.
    – jynus
    Commented Jul 24, 2014 at 13:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.