Databases
StackOverflow2013
Postsdbo
PONeed help optimizing a lat/Lon geo search for mysql
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PONeed help optimizing a lat/Lon geo search for mysql
    primarykey
    data
    text
    <p>I have a mysql (5.0.22) myisam table with roughly 300k records in it and I want to do a lat/lon distance search within a five mile radius. </p> <p>I have an index that covers the lat/lon fields and is fast (milisecond response) when I just select for lat/lon. But when I select for additional fields in the table is slows down horribly to 5-8 seconds.</p> <p>I'm using myisam to take advantage of fulltext search. The other indexes perform well (e.g. select * from Listing where slug = 'xxxxx').</p> <p>How can I optimize my query, table or index to speed things up?</p> <p>My schema is:</p> <pre><code>CREATE TABLE `Listing` ( `id` int(10) unsigned NOT NULL auto_increment, `name` varchar(125) collate utf8_unicode_ci default NULL, `phone` varchar(18) collate utf8_unicode_ci default NULL, `fax` varchar(18) collate utf8_unicode_ci default NULL, `email` varchar(55) collate utf8_unicode_ci default NULL, `photourl` varchar(55) collate utf8_unicode_ci default NULL, `thumburl` varchar(5) collate utf8_unicode_ci default NULL, `website` varchar(85) collate utf8_unicode_ci default NULL, `categoryid` int(10) unsigned default NULL, `addressid` int(10) unsigned default NULL, `deleted` tinyint(1) default NULL, `status` int(10) unsigned default '2', `parentid` int(10) unsigned default NULL, `organizationid` int(10) unsigned default NULL, `listinginfoid` int(10) unsigned default NULL, `createuserid` int(10) unsigned default NULL, `createdate` datetime default NULL, `lasteditdate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `lastedituserid` int(10) unsigned default NULL, `slug` varchar(155) collate utf8_unicode_ci default NULL, `aclid` int(10) unsigned default NULL, `alt_address` varchar(80) collate utf8_unicode_ci default NULL, `alt_website` varchar(80) collate utf8_unicode_ci default NULL, `lat` decimal(10,7) default NULL, `lon` decimal(10,7) default NULL, `city` varchar(80) collate utf8_unicode_ci default NULL, `state` varchar(10) collate utf8_unicode_ci default NULL, PRIMARY KEY (`id`), KEY `idx_fetch` USING BTREE (`slug`,`deleted`), KEY `idx_loc` (`state`,`city`), KEY `idx_org` (`organizationid`,`status`,`deleted`), KEY `idx_geo_latlon` USING BTREE (`status`,`lat`,`lon`), FULLTEXT KEY `idx_name` (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC; </code></pre> <p>My query is:</p> <pre><code>SELECT Listing.name, Listing.categoryid, Listing.lat, Listing.lon , 3956 * 2 * ASIN(SQRT( POWER(SIN((Listing.lat - 37.369195) * pi()/180 / 2), 2) + COS(Listing.lat * pi()/180) * COS(37.369195 * pi()/180) * POWER(SIN((Listing.lon --122.036849) * pi()/180 / 2), 2) )) rawgeosearchdistance FROM Listing WHERE Listing.status = '2' AND ( Listing.lon between -122.10913433498 and -121.96456366502 ) AND ( Listing.lat between 37.296909665016 and 37.441480334984) HAVING rawgeosearchdistance &lt; 5 ORDER BY rawgeosearchdistance ASC; </code></pre> <p>Explain plan without geosearch:</p> <pre> +----+-------------+------------+-------+-----------------+-----------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len |ref | rows | Extra | +----+-------------+------------+-------+-----------------+-----------------+---------+------+------+-------------+ | 1 | SIMPLE | Listing | range | idx_geo_latlon | idx_geo_latlon | 19 | NULL | 453 | Using where | +----+-------------+------------+-------+-----------------+-----------------+---------+------+------+-------------+ </pre> <p>Explain plan with geosearch:</p> <pre> +----+-------------+------------+-------+-----------------+-----------------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+-----------------+-----------------+---------+------+------+-----------------------------+ | 1 | SIMPLE | Listing | range | idx_geo_latlon | idx_geo_latlon | 19 | NULL | 453 | Using where; Using filesort | +----+-------------+------------+-------+-----------------+-----------------+---------+------+------+-----------------------------+ </pre> <p>Here's the explain plan with the covering index. Having the columns in the correct order made a big difference:</p> <pre> +----+-------------+--------+-------+---------------+---------------+---------+------+--------+------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------------+---------+------+--------+------------------------------------------+ | 1 | SIMPLE | Listing | range | idx_geo_cover | idx_geo_cover | 12 | NULL | 453 | Using where; Using index; Using filesort | +----+-------------+--------+-------+---------------+---------------+---------+------+--------+------------------------------------------+ </pre> <p>Thank you!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJeff
    1. USJeff
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PONeed help optimizing a lat/Lon geo search for mysql
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PONeed help optimizing a lat/Lon geo search for mysql
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PONeed help optimizing a lat/Lon geo search for mysql
      1. USngeek
      1. VTFavorite
    1. COPost the explain plan for the fast and slow queries.
      singulars
      1. PONeed help optimizing a lat/Lon geo search for mysql
      1. USjonstjohn
    2. COLooks like you have way too many columns in that one table. You can squeeze some performance out of your queries by normalizing your data structure a bit. :)
      singulars
      1. PONeed help optimizing a lat/Lon geo search for mysql
      1. UStom
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload