Since most of the rows have link_id = NULL, using that as the first
condition in the WHERE clause does not help MySQL reduce the number of
rows it needs to check (therefor MySQL prefers to do a full table scan).
My suggestion is create a composite index on (latitude, longitude,
link_id) and change your queries to
SELECT * FROM crumb WHERE latitude > 39 AND longitude > -98 AND link_id
IS NULL LIMIT 10000;
I think this will use the composite index, thus avoiding a full table scan.
See http://dev.mysql.com/doc/mysql/en/mysql-indexes.html for in depth
info on how MySQL uses indexes.
As a side note, it's a good idea to run EXPLAIN on your queries to see
what index they are using, what cardinality the index has, and pay close
attention to the "Extra" field in the results of EXPLAIN. That field
will tell you if MySQL thinks it has to do a full table scan, create a
temporary table, etc.
Regards,
Devananda vdv
Chris Kantarjiev wrote:
I'm looking at the stats on one of our servers and trying to understand
why Handler_read_rnd_next is so high. It's 256.5M right now, which is
about 10x the total number of reported queries.
The machine is being used, almost entirely, for queries of the form:
select * from crumb
where link_id is null
and latitude > 39
and longitude > -98
limit 10000;
link_id is indexed. There are about 8 million rows in the table,
and most of them have link_id = null right now. latitude and longitude
are not indexed - but my understanding is that mySQL will only
use one index, and link_id is the interesting one for us.
Are the latitude and longitude qualifiers the cause of the table scans?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
