On Jul 25, 2005, at 3:47 PM, Chris Kantarjiev wrote:
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?
Yes, they almost certainly are the cause of the problem. A query may
only use one index, but the table can have several, and the MySQL
Optimizer will choose the most appropriate index for the query it is
running. In a case such as this where most entries have a null
link_id you are hurting from having no index covering the other
columns. If this was all the table was going to do and all your
queries were of this form, you could make an index on link_ID,
latitude, longitude... as long as most everything is null it's the
equivalent of using an index on latitude, longitude... but as things
change (I assume you don't expect them to stay null) your one index
will accommodate that...
However... when you say what's important to you is the link_id, I
assume you mean that's what is important in the result... not what is
important in the search itself (as it clearly isn't now if they are
mostly null). The thing is to remember, while a query may use only
one index, a MySQL table can have many (don't go nuts here), so by
adding an index for latitude, longitude you are buying yourself a
bunch of performance.
Beware of course... too many indexes or too complicated and they can
be a performance issue in their own right. The trick is to put in the
right indexes for your data and for your queries, don't just add
indexes for indexing sake.
Best Regards, Bruce
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
