Mysql – Slow MySQL query with float using BETWEEN and ORDER BY

MySQL

Trying to figure out whether it's the BETWEEN being used on a float or the ORDER BY and the lack of an index that is making this query slow.

First the tables used in the BETWEEN and ORDER BY:

mysql> explain objects;
+---------------------------+----------------------+------+-----+---------+----------------+
| Field                     | Type                 | Null | Key | Default | Extra          |
+---------------------------+----------------------+------+-----+---------+----------------+
| object_id                 | int(10) unsigned     | NO   | PRI | NULL    | auto_increment |
| type_id                   | smallint(5) unsigned | NO   | MUL | 0       |                |
| detached                  | tinyint(1)           | YES  |     | 0       |                |
| private_attach            | varchar(16)          | YES  |     | No      |                |
| construction              | varchar(4)           | NO   |     | 0       |                |
| last_edit                 | datetime             | YES  | MUL | NULL    |                |
| creation                  | datetime             | YES  | MUL | NULL    |                |
| last_edit_user_id         | int(10) unsigned     | NO   |     | 0       |                |
| created_user_id           | int(10) unsigned     | NO   | MUL | 0       |                |
| object_name               | varchar(255)         | YES  | MUL | NULL    |                |
| query_name                | varchar(255)         | YES  |     | NULL    |                |
| object_body_ft            | mediumtext           | YES  |     | NULL    |                |
| object_body_nft           | text                 | YES  | MUL | NULL    |                |
| media_file                | varchar(255)         | YES  |     | NULL    |                |
| latitude                  | float(8,5)           | YES  | MUL | NULL    |                |
| longitude                 | float(8,5)           | YES  | MUL | NULL    |                |
| country                   | varchar(255)         | YES  | MUL | NULL    |                |
| state_province            | varchar(255)         | YES  | MUL | NULL    |                |
| county                    | varchar(100)         | YES  |     | NULL    |                |
| elevation                 | float                | YES  |     | NULL    |                |
| continent                 | varchar(255)         | YES  |     | NULL    |                |
| tags                      | text                 | YES  | MUL | NULL    |                |
| tag_text                  | text                 | YES  |     | NULL    |                |
| route_type                | varchar(255)         | YES  |     | NULL    |                |
| activities                | varchar(255)         | YES  |     | NULL    |                |
| seasons                   | varchar(255)         | YES  |     | NULL    |                |
| time_required             | varchar(255)         | YES  |     | NULL    |                |
| difficulty                | varchar(255)         | YES  |     | NULL    |                |
| num_pitches               | int(11)              | YES  |     | NULL    |                |
| date_climbed              | date                 | YES  |     | NULL    |                |
| primary_photo             | varchar(60)          | YES  |     | NULL    |                |
| grade                     | varchar(255)         | YES  |     | NULL    |                |
| rock_difficulty           | varchar(255)         | YES  |     | NULL    |                |
| obj_type_name             | varchar(255)         | YES  |     | NULL    |                |
| is_primary_photo_user_set | tinyint(1)           | NO   |     | 0       |                |
| province                  | varchar(255)         | YES  |     | NULL    |                |
| show_toc                  | varchar(4)           | NO   |     | 0       |                |
| gpx_file                  | varchar(25)          | YES  |     | NULL    |                |
+---------------------------+----------------------+------+-----+---------+----------------+

mysql> explain object_scores;
+--------------------+------------------+------+-----+---------+-------+
| Field              | Type             | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+---------+-------+
| object_id          | int(10) unsigned | NO   | PRI | 0       |       |
| hits               | int(10) unsigned | NO   | MUL | 0       |       |
| score              | float            | NO   | MUL | 0       |       |
| weighted_num_votes | float            | NO   |     | 0       |       |
| weighted_vote_sum  | float            | NO   |     | 0       |       |
+--------------------+------------------+------+-----+---------+-------+

And now the query:

SELECT SQL_CALC_FOUND_ROWS 
`objects`.`query_name`, 
`objects`.`primary_photo`, 
`object_scores`.`hits` AS `hits`, 
`object_scores`.`score` AS `score`, 
`types`.`type_name`, 
`users`.`username` AS `contributor`, 
`objects`.`object_id`, 
`users`.`user_id` AS `contributor_id`, 
`objects`.`object_name`, 
`objects`.`media_file`, 
`objects`.`last_edit`, 
`objects`.`creation`, 
`objects`.`created_user_id`, 
`objects`.`construction`, 
`objects`.`latitude`, 
`objects` .`longitude`, 
ABS(3963.1 * ACOS(COS(RADIANS(`objects`.`latitude`)) * COS(RADIANS(`objects`.`longitude`)) * COS(RADIANS(45.89957)) * COS(RADIANS(7.89368)) + COS(RADIANS(`objects`.`latitude`)) * SIN(RADIANS(`objects`.`longitude`)) * COS(RADIANS(45.89957)) * SIN(RADIANS(7.89368)) + SIN(RADIANS(`objects`.`latitude`)) * SIN(RADIANS(45.89957)))) AS `distance` 

FROM `users` 
JOIN `types` 
JOIN `objects` FORCE INDEX (`longitude`) 
JOIN `object_scores` ON `object_scores`.`object_id`=`objects`.`object_id` 

WHERE `users`.`user_id`=`objects`.`created_user_id` 
AND `types`.`type_id`=`objects`.`type_id` 
AND `objects`.`construction`!='Yes' 
AND `objects`.`detached`!=1 
AND `objects`.`latitude` IS NOT NULL 
AND `objects` .`longitude` IS NOT NULL 
AND `objects`.`latitude` BETWEEN 44.453844928718 AND 47.345295071282 
AND `objects`.`longitude` BETWEEN 5.8162446056357 AND 9.9711153943643 
AND ABS(3963.1 * ACOS(COS(RADIANS(`objects`.`latitude`)) * COS(RADIANS(`objects`.`longitude`)) * COS(RADIANS(45.89957)) * COS(RADIANS(7.89368)) + COS(RADIANS(`objects`.`latitude`)) * SIN(RADIANS(`objects`.`longitude`)) * COS(RADIANS(45.89957)) * SIN(RADIANS(7.89368)) + SIN(RADIANS(`objects`.`latitude`)) * SIN(RADIANS(45.89957)))) < 100 
AND `objects`.`type_id`=3 

ORDER BY `object_scores`.`score` ASC LIMIT 20310, 30;

And the EXPLAIN on the query:

+----+-------------+---------------+--------+---------------+-----------+---------+-----------------------------+-------+---------------------------------+
| id | select_type | table         | type   | possible_keys | key       | key_len | ref                         | rows  | Extra                           |
+----+-------------+---------------+--------+---------------+-----------+---------+-----------------------------+-------+---------------------------------+
|  1 | SIMPLE      | types         | const  | PRIMARY       | PRIMARY   | 2       | const                       |     1 | Using temporary; Using filesort |
|  1 | SIMPLE      | objects       | range  | longitude     | longitude | 5       | NULL                        | 59316 | Using where                     |
|  1 | SIMPLE      | users         | eq_ref | PRIMARY       | PRIMARY   | 4       | cms.objects.created_user_id |     1 |                                 |
|  1 | SIMPLE      | object_scores | eq_ref | PRIMARY       | PRIMARY   | 4       | cms.objects.object_id       |     1 |                                 |
+----+-------------+---------------+--------+---------------+-----------+---------+-----------------------------+-------+---------------------------------+

The objects table has approximately 760,000 entries in it, as does the object_scores table. I'm trying to figure out why so many rows are examined (59316) while doing the query. I suspect it has something to do with the indices on the latitude or longitude or indices with relation to the ORDER BY.

I've tried removing the FORCE INDEX (longitude), but that results in a slower query in most cases.

Suggestions anyone?

Best Answer

No index in the objects table is selective enough to restrict the search to a small number of rows, that's why the query is slow. The engine has to either do a full scan of the table or use an index (which is probably inefficient).

If it uses an index, like the one on (longitude), it can find (fast, using the index) the 59316 rows that have longitude BETWEEN 5.8162446056357 AND 9.9711153943643 but then the corresponding 59316 rows have to be read from the table (and they are probably scattered around the table so a very large portion of the table has to be read. Further all these rows have to be checked for all the other conditions you have in the WHERE and then ordered (Using filesort).

Your best bet is to add a spatial index. This is possible only if the table uses the MyISAM engine (otherwise you could split the table into 2, one as it is with all the columns and using InnoDB and one using MyiSAM and only the columns needed for the spatial index).

Related Solutions

Mysql – Are two indexes needed

An index can seek by a subset of characters, as long as you're searching from the left. E.g., "Inter%" can seek, "%net" will not.

However, the first character is not necessarily the character under which the article would be sorted. "The Internet" should go under "I", not "T". You probably need two fields, DisplayTitle and SortTitle; a single-character index on the latter may be worthwhile, but most likely a full-length index will be just fine.

Indexes are typically B-trees, and a seek will jump to the right location about equally quickly whether you have 10 or 100 entries per page. Scans are another matter, but I'd start with the simplest solution and add an extra index only if performance proves inadequate in practice.

Mysql – Slow complex query with group/order

I can see couple things that should improve your query performance.

1 As you already found out there is absolutely no need to join mentioncache. Using EXISTS seems more natural (or IN as you did, but EXISTS may work better from performance point of view).

2 DATE(m.indexed) BETWEEN "2012-09-16" AND "2012-10-16" can be rewritten to m.indexed between "2012-09-16" AND "2012-10-16 23:59:59", so mysql can use index.

3 urlinfluranks doesn't seem to be used anywhere except in LEFT JOIN, why do you need it?

4 f.foreign_id can be either null or m.id, and this is the only reference to favoureditems table, I'd rather use subquery in this case.

Finally, I think you can get the same results without GROUP BY m.id (as far as I understood , mentions.id a primary key).

SELECT   
m.id, m.title, m.title_text, m.content_text, m.url,m.root_url,m.sub_type,m.indexed,  
CASE 
 WHEN EXISTS 
    (SELECT NULL FROM favoureditems f WHERE f.model = "Mention" 
    AND f.foreign_id = m.id AND f.owner_id = 803) THEN m.id 
END AS f.foreign_id,
, v.foreign_id, v.created, mfs.score,  
Image.id,Image.model,Image.foreign_key, Image.dirname,Image.basename,  
(REPLACE(REPLACE(m.host_url, 'http://www.', ''), 'http://', '')) AS Mention__plain_url  
FROM mentions AS m  

LEFT JOIN 
(
  SELECT id,model,foreign_key,dirname,basename 
  FROM attachments Image  
  WHERE model = 'Mention'
  GROUP BY foreign_key
 )Image  ON (Image.foreign_key = m.id)      

LEFT JOIN 
(
   SELECT v.foreign_id, v.created 
   FROM visiteditems AS v  
   WHERE (v.model = "Mention"  AND v.owner_id = 803)  
    GROUP BY v.foreign_id
)v ON (v.foreign_id = m.id)
LEFT JOIN 
(
   SELECT mention_id,score
   FROM mentionfeedscores mfs  
   WHERE mfs.feed_id = '474737584865424564398208323289092'
   GROUP BY mention_id
)mfs ON (mfs.mention_id = m.id )

WHERE m.indexed BETWEEN "2012-09-16" AND "2012-10-16 23:59:59"  
   AND EXISTS 
  (
     SELECT NULL FROM mentioncache mc  
      WHERE mc.mention_id = m.id AND mc.profile_id = 803  
   )    
ORDER BY m.indexed DESC  
LIMIT 10

Best Answer

Related Solutions

Mysql – Are two indexes needed

Mysql – Slow complex query with group/order

Related Question