Re: query not using index
Posted by: gigi kent
Date: November 08, 2013 09:42AM

Yes, that is the query in question.


>> If you are JOINing the two tables on `uid`, then it does not make sense to have it be AUTO_INCREMENT in _both_ tables.

Seems like foreign key constraints on table do not like that:

ERROR 1025 (HY000): Error on rename of './tigasedb/#sql-3e22_25' to './tigasedb/xgate_geoloc' (errno: 150)

>> No. It can look in the part of the index bounded by `lat`, but it must scan all those records, checking each for `lon` to match.

I can understand that, however, shouldn't it ultimately point out that it *is* utilizing the index ?
Tried with both (lat, time) and (lon, time) indexes for the matter, still no joy. Explain plan does not indicate towards any key usage :|

+----+-------------+-------+--------+-----------------+---------+---------+----------------+--------+----------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+--------+-----------------+---------+---------+----------------+--------+----------+-----------------------------+
| 1 | SIMPLE | g | ALL | uk_uid,lon_time | NULL | NULL | NULL | 199926 | 50.00 | Using where; Using filesort |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | tigasedb.g.uid | 1 | 100.00 | |
+----+-------------+-------+--------+-----------------+---------+---------+----------------+--------+----------+-----------------------------+

Keeping the (lon,time) index and taking out the known trouble maker SQL_CALC_FOUND_ROWS from the query yields some other results:

+----+-------------+-------+--------+-----------------+-----------+---------+----------------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+--------+-----------------+-----------+---------+----------------+------+----------+-------------+
| 1 | SIMPLE | g | index | uk_uid,lon_time | time_lang | 14 | NULL | 100 | 99963.00 | Using where |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | tigasedb.g.uid | 1 | 100.00 | |
+----+-------------+-------+--------+-----------------+-----------+---------+----------------+------+----------+-------------+

Howver, as per http://stackoverflow.com/questions/4083632/can-mysql-use-index-in-a-range-query-with-order-by the bottom of my problem emerges. Composite indexes are only kept ordered by just the first part of the key, so any additional range scans / sorting has to be done without it.

>>You could improve things by using a subquery to find the uids you need, then JOIN to get g.*

Well I could... if I have had the uid as a firsthand filtering condition, which I don't.

>>This is part of the secret sauce used in the link I gave you.

That article felt over my head when I read it a while ago. What I would think now is to map my lat/lon pairs along some space partitioning curve such as the hilbert curve and only have one column to range scan instead of two. Of course, that would make sense if and only if the coordinates scan is actually more time consuming than the filesort caused by time ordering. Otherwise just arrange the index to be used for just sorting by time and be done with it. Is this the right line of thought ? If so, how do I measure time taken to range scan by lat/lon vs time taken to filesort by time ?

Thanks again!

Options: ReplyQuote


Subject
Written By
Posted
October 31, 2013 07:40AM
November 02, 2013 02:58PM
November 03, 2013 12:43PM
November 04, 2013 05:39AM
November 05, 2013 09:53PM
Re: query not using index
November 08, 2013 09:42AM
November 09, 2013 03:42PM
November 12, 2013 09:54PM
November 13, 2013 06:31PM
November 13, 2013 08:56PM
November 18, 2013 12:20AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.