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!