mysql query speed. why query from memory tables with tmp table slower than direct select?

Question

i'm a bit confused of such mysql behaviour. one query, with ORDER BY clause creates tmp table (as shown in show profile) and runs faster that the same query without order by with doesn't create tmp table. why? and why does "sorting" appears in profile output while no ORDER BY present?

so, from the very beginning. i have mysql5.1 on amazon m1.small instance.

i have 4 tables like:

CREATE TABLE `meetings_m` (
  `id` int(11) unsigned NOT NULL DEFAULT '0',
  `name` varchar(255) DEFAULT NULL,
  `address1` varchar(255) DEFAULT NULL,
  `address2` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `state` varchar(50) DEFAULT NULL,
  `zip` varchar(255) DEFAULT NULL,
  `country` varchar(255) DEFAULT NULL,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  `user_id` int(11) unsigned NOT NULL,
  `image_url` varchar(250) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1 

CREATE TABLE `meeting_times_m` (
  `id` int(11) unsigned NOT NULL DEFAULT '0',
  `meeting_id` int(11) unsigned NOT NULL,
  `day` varchar(255) NOT NULL,
  `time` time NOT NULL,
  `name` varchar(50) NOT NULL,
  `format` varchar(255) DEFAULT NULL,
  `notes` varchar(255) NOT NULL,
  `program` varchar(255) NOT NULL,
  `cancelled` varchar(10) NOT NULL DEFAULT 'False',
  PRIMARY KEY (`id`),
  KEY `meeting_id` (`meeting_id`),
  KEY `program` (`program`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1 

CREATE TABLE `real_meeting_tags_m` (
  `id` int(10) unsigned NOT NULL DEFAULT '0',
  `meeting_id` int(10) unsigned NOT NULL,
  `tag_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `meeting_id` (`meeting_id`),
  KEY `tag_id` (`tag_id`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1

CREATE TABLE `tags_m` (
  `id` int(11) unsigned NOT NULL DEFAULT '0',
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1

========================= now i run

SELECT SQL_CALC_FOUND_ROWS 
    Meeting.*, 
    3959.87*acos(sin(radians(37.7858))*sin(radians(latitude))+cos(radians(37.7858))*cos(radians(latitude))*cos(radians(-122.406-longitude))) as distance, 
    count(`MeetingTag`.`id`) as tag_count, 
    group_concat(distinct Tag.name) as tags, 
    group_concat(distinct Tag.id) as tag_ids 
FROM 
    `meetings_m` AS `Meeting` 
    LEFT JOIN `meeting_times_m` AS `MeetingTime` ON (`MeetingTime`.`meeting_id` = `Meeting`.`id`) 
    INNER JOIN `real_meeting_tags_m` AS `MeetingTag` ON (`MeetingTag`.`meeting_id`=`Meeting`.`id` AND (`MeetingTag`.`tag_id` in (9,8,16,14,4,2,5,15,11,7,10,13,3,6))) 
    LEFT JOIN `tags_m` AS `Tag` ON (`MeetingTag`.`tag_id` = `Tag`.`id`)  
WHERE 
    3959.87*acos(sin(radians(37.7858))*sin(radians(latitude))+cos(radians(37.7858))*cos(radians(latitude))*cos(radians(-122.406-longitude))) <= '204.0' 
    AND `MeetingTime`.`program` = ('AA') AND ((`Meeting`.`name` LIKE 'AA%') OR (`MeetingTime`.`program` LIKE 'AA%'))  
GROUP BY 
    `MeetingTag`.`meeting_id`  
ORDER BY `distance` ASC  
LIMIT 22

this query runs 36 seconds and its profile is

| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000027 |
| checking query cache for query | 0.000217 |
| checking permissions           | 0.000005 |
| checking permissions           | 0.000002 |
| checking permissions           | 0.000003 |
| checking permissions           | 0.000005 |
| Opening tables                 | 0.000019 |
| System lock                    | 0.000004 |
| Table lock                     | 0.000059 |
| init                           | 0.000075 |
| optimizing                     | 0.000030 |
| statistics                     | 0.000081 |
| preparing                      | 0.000035 |
| Creating tmp table             | 0.000059 |
| executing                      | 0.000004 |
| Copying to tmp table           | 0.352465 |
| Creating sort index            | 0.001807 |
| Copying to group table         | 0.005540 |
| Sorting result                 | 0.000776 |
| Sending data                   | 0.002716 |
| end                            | 0.000006 |
| removing tmp table             | 0.000009 |
| end                            | 0.000003 |
| removing tmp table             | 0.000425 |
| end                            | 0.000007 |
| query end                      | 0.000004 |
| freeing items                  | 0.000020 |
| removing tmp table             | 0.000005 |
| freeing items                  | 0.000005 |
| removing tmp table             | 0.000004 |
| freeing items                  | 0.000526 |
| storing result in query cache  | 0.000010 |
| logging slow query             | 0.000003 |
| cleaning up                    | 0.002318 |

0.35 seconds on copying to tmp table. explain is

+----+-------------+-------------+--------+--------------------+------------+---------+----------------------------------+------+----------------------------------------------+
| id | select_type | table       | type   | possible_keys      | key        | key_len | ref                              | rows | Extra                                        |
+----+-------------+-------------+--------+--------------------+------------+---------+----------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | MeetingTime | ref    | meeting_id,program | program    | 257     | const                            | 7129 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | MeetingTag  | ref    | meeting_id,tag_id  | meeting_id | 4       | stepsaway.MeetingTime.meeting_id |    2 | Using where                                  |
|  1 | SIMPLE      | Tag         | eq_ref | PRIMARY            | PRIMARY    | 4       | stepsaway.MeetingTag.tag_id      |    1 |                                              |
|  1 | SIMPLE      | Meeting     | eq_ref | PRIMARY,name       | PRIMARY    | 4       | stepsaway.MeetingTag.meeting_id  |    1 | Using where                                  |
+----+-------------+-------------+--------+--------------------+------------+---------+----------------------------------+------+----------------------------------------------+

so i do want to make query faster. so i think that skipping tmp table will help. after reading some mans i'm changing group by from MeetingTag.meeting_id to MeetingTime.meeting_id (as they are same) and removing order by clause

now query is

SELECT SQL_CALC_FOUND_ROWS 
    Meeting.*, 
    3959.87*acos(sin(radians(37.7858))*sin(radians(latitude))+cos(radians(37.7858))*cos(radians(latitude))*cos(radians(-122.406-longitude))) as distance, 
    count(`MeetingTag`.`id`) as tag_count, 
    group_concat(distinct Tag.name) as tags, 
    group_concat(distinct Tag.id) as tag_ids 
FROM 
    `meetings_m` AS `Meeting` 
    LEFT JOIN `meeting_times_m` AS `MeetingTime` ON (`MeetingTime`.`meeting_id` = `Meeting`.`id`) 
    INNER JOIN `real_meeting_tags_m` AS `MeetingTag` ON (`MeetingTag`.`meeting_id`=`Meeting`.`id` AND (`MeetingTag`.`tag_id` in (9,8,16,14,4,2,5,15,11,7,10,13,3,6))) 
    LEFT JOIN `tags_m` AS `Tag` ON (`MeetingTag`.`tag_id` = `Tag`.`id`)  
WHERE 
    3959.87*acos(sin(radians(37.7858))*sin(radians(latitude))+cos(radians(37.7858))*cos(radians(latitude))*cos(radians(-122.406-longitude))) <= '204.0' 
    AND `MeetingTime`.`program` = ('AA') AND ((`Meeting`.`name` LIKE 'AA%') OR (`MeetingTime`.`program` LIKE 'AA%'))  
GROUP BY 
    `MeetingTime`.`meeting_id`  
LIMIT 22

explain is

+----+-------------+-------------+--------+--------------------+------------+---------+----------------------------------+------+-----------------------------+
| id | select_type | table       | type   | possible_keys      | key        | key_len | ref                              | rows | Extra                       |
+----+-------------+-------------+--------+--------------------+------------+---------+----------------------------------+------+-----------------------------+
|  1 | SIMPLE      | MeetingTime | ref    | meeting_id,program | program    | 257     | const                            | 7129 | Using where; Using filesort |
|  1 | SIMPLE      | MeetingTag  | ref    | meeting_id,tag_id  | meeting_id | 4       | stepsaway.MeetingTime.meeting_id |    2 | Using where                 |
|  1 | SIMPLE      | Tag         | eq_ref | PRIMARY            | PRIMARY    | 4       | stepsaway.MeetingTag.tag_id      |    1 |                             |
|  1 | SIMPLE      | Meeting     | eq_ref | PRIMARY,name       | PRIMARY    | 4       | stepsaway.MeetingTag.meeting_id  |    1 | Using where                 |
+----+-------------+-------------+--------+--------------------+------------+---------+----------------------------------+------+-----------------------------+

and execution time is 42second. profile is

+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000026 |
| checking query cache for query | 0.000211 |
| checking permissions           | 0.000005 |
| checking permissions           | 0.000003 |
| checking permissions           | 0.000003 |
| checking permissions           | 0.000005 |
| Opening tables                 | 0.000019 |
| System lock                    | 0.000003 |
| Table lock                     | 0.000058 |
| init                           | 0.000075 |
| optimizing                     | 0.000031 |
| statistics                     | 0.000082 |
| preparing                      | 0.000036 |
| executing                      | 0.000051 |
| Sorting result                 | 0.145705 |
| Sending data                   | 0.271131 |
| end                            | 0.000016 |
| removing tmp table             | 0.000008 |
| end                            | 0.000006 |
| removing tmp table             | 0.000004 |
| end                            | 0.000007 |
| query end                      | 0.000004 |
| freeing items                  | 0.000619 |
| storing result in query cache  | 0.000010 |
| logging slow query             | 0.000003 |
| cleaning up                    | 0.000006 |
+--------------------------------+----------+

sorting results and sending data take even longer than creating tmp table!!?? but why??? i'm working with MEMORY table.. why does it take so long? and why does it sorts at all? i remove order by clause

You have a `GROUP BY`. Try adding `ORDER BY NULL` to remove the implicit sort (not that this solves your problem, the `ORDER BY distance` is I guess the main point of the query). — ypercubeᵀᴹ, Mar 26 '12 at 23:39
Instead of Memory engine, you could try MyISAM and changing those latitide-logitude columns into a (geometry) [`POINT`](http://dev.mysql.com/doc/refman/5.1/en/spatial-extensions.html) column and adding a spatial index. — ypercubeᵀᴹ, Mar 26 '12 at 23:45
And a related question: [Fastest distance lookup given latitude/longitude?](http://stackoverflow.com/questions/1006654/fastest-distance-lookup-given-latitude-longitude) — ypercubeᵀᴹ, Mar 27 '12 at 00:01
after all optimizations, now most of time is spent while copying to tmp table. and this copy occurs because of sort by distance. you're right. i'll try spatial indexes. thanks — Demo_S, Mar 27 '12 at 10:30

mysql query speed. why query from memory tables with tmp table slower than direct select?

0 Answers0