Hi,
I hope this is the right place to ask a question about query optimization.
Background:
I have a database which has events, which occur in places (listings). Places
have addresses, and addresses belong to a city. I can select the latest
event within a particular city very efficiently (less than 2ms), but
selecting the latest listing within a city is REALLY slow (10-20 seconds)
despite being almost a subset of the event query.
I have been working on this for about a day, and have tried all sorts of
tweaks to the indexes but to no avail. I always seem to end up with 'using
temporary; using filesort' as the 'extra' content in the explain result. If
anyone has a suggestion for what I might do to fix this, I'd really
appreciate it. If not, I could further de-normalize the database for
performance reasons, but I would feel dirty for doing so.
Here is the fast query (select the latest event within a particular city),
and it's explain.
SELECT
events.*, listings.*, addresses.*
FROM
`events`
LEFT OUTER JOIN
`listings` ON `listings`.id = `events`.listing_id
LEFT OUTER JOIN
`addresses` ON `addresses`.addressable_id = `listings`.id
AND
`addresses`.addressable_type = 'Listing'
WHERE (addresses.parent_city_id = 3)
ORDER BY events.id DESC LIMIT 1
+----+-------------+-----------+--------+----------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------+---------+------------------------------+------+-------------+
| id | select_type | table | type | possible_keys
| key
| key_len | ref | rows | Extra |
+----+-------------+-----------+--------+----------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------+---------+------------------------------+------+-------------+
| 1 | SIMPLE | events | index | index_events_on_listing_id
| PRIMARY
| 4 | NULL | 1 | |
| 1 | SIMPLE | listings | eq_ref | PRIMARY
| PRIMARY
| 4 | ratemyarea.events.listing_id | 1 | Using where |
| 1 | SIMPLE | addresses | ref |
index_addresses_on_parent_city_id_and_addressable_type,index_addresses_on_addressable_type_and_addressable_id,addressable_id_type_city
| index_addresses_on_addressable_type_and_addressable_id | 773 | const,
ratemyarea.listings.id | 1 | Using where |
+----+-------------+-----------+--------+----------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------+---------+------------------------------+------+-------------+
Here is the slow query (select the latest listing within a particular city),
and it's explain
SELECT
listings.*, addresses.*
FROM
`listings`
LEFT OUTER JOIN
`addresses` ON `addresses`.addressable_id = `listings`.id
AND
`addresses`.addressable_type = 'Listing'
WHERE (addresses.parent_city_id = 3)
ORDER BY listings.id DESC LIMIT 1
+----+-------------+-----------+--------+----------------------------------------------------------------------------------------------------------------------------------------+--------------------------+---------+-------------------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys
| key | key_len | ref
| rows | Extra
|
+----+-------------+-----------+--------+----------------------------------------------------------------------------------------------------------------------------------------+--------------------------+---------+-------------------------------------+-------+----------------------------------------------+
| 1 | SIMPLE | addresses | ref |
index_addresses_on_parent_city_id_and_addressable_type,index_addresses_on_addressable_type_and_addressable_id,addressable_id_type_city
| addressable_id_type_city | 773 | const,const |
25680 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | listings | eq_ref | PRIMARY
| PRIMARY | 4 |
ratemyarea.addresses.addressable_id | 1 |
|
+----+-------------+-----------+--------+----------------------------------------------------------------------------------------------------------------------------------------+--------------------------+---------+-------------------------------------+-------+----------------------------------------------+
Here is the structure of the tables:
CREATE TABLE `addresses` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`postal_code` varchar(255) DEFAULT NULL,
`addressable_type` varchar(255) DEFAULT NULL,
`addressable_id` int(11) DEFAULT NULL,
`parent_city_id` int(11) DEFAULT NULL,
`lat` decimal(15,10) DEFAULT NULL,
`lng` decimal(15,10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_addresses_on_lat_and_lng` (`lat`,`lng`),
KEY `index_addresses_on_parent_city_id_and_addressable_type`
(`parent_city_id`,`addressable_type`),
KEY `index_addresses_on_addressable_type_and_addressable_id`
(`addressable_type`,`addressable_id`)
) ENGINE=InnoDB AUTO_INCREMENT=120513 DEFAULT CHARSET=utf8;
# Dump of table cities
# ------------------------------------------------------------
CREATE TABLE `cities` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`country_id` int(11) NOT NULL,
`lat` decimal(15,10) NOT NULL,
`lng` decimal(15,10) NOT NULL,
PRIMARY KEY (`id`),
KEY `index_cities_on_name` (`name`),
KEY `index_cities_on_country_id` (`country_id`),
KEY `index_cities_on_lat_and_lng` (`lat`,`lng`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
# Dump of table events
# ------------------------------------------------------------
CREATE TABLE `events` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`listing_id` int(11) NOT NULL,
`description` text,
`title` varchar(150) NOT NULL,
PRIMARY KEY (`id`),
KEY `index_events_on_title` (`title`),
KEY `index_events_on_listing_id` (`listing_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6359 DEFAULT CHARSET=utf8;
# Dump of table listings
# ------------------------------------------------------------
CREATE TABLE `listings` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`latitude` decimal(15,10) DEFAULT NULL,
`longitude` decimal(15,10) DEFAULT NULL,
`description` text,
PRIMARY KEY (`id`),
KEY `index_listings_on_place` (`name`),
KEY `index_listings_on_latitude_and_longitude` (`latitude`,`longitude`)
) ENGINE=InnoDB AUTO_INCREMENT=109358 DEFAULT CHARSET=utf8;
# Dump of table users
# ------------------------------------------------------------
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`email` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_users_on_email` (`email`),
KEY `index_users_on_latitude_and_longitude` (`latitude`,`longitude`)
) ENGINE=InnoDB AUTO_INCREMENT=1972 DEFAULT CHARSET=utf8;
