MySQL Indexes

[Avishai]

Hey everyone,

I've been using the data dumps and am having some issues trying to
figure out what the optimal indexes are for the geonames table. I'm
trying to do simple SELECTs ordered by country and population, but in
production this often takes forever, once you page towards the end of
the result set, like this:

Statement Analysis
Query
SELECT * FROM `places` WHERE (`places`.`country_id` = 'BR') ORDER BY
places.population DESC LIMIT 78600, 10
Obfuscated Query
SELECT * FROM `places` WHERE (`places`.`country_id` = ?) ORDER BY
places.population DESC LIMIT ?, ?
Duration 6,708 ms #<-- can be ~10x as long in production, especially
on bigger countries like US
Id 1
Select Type SIMPLE
Table places
Type ref
Possible Keys
index_places_on_country_id,index_places_on_country_id_and_population
Key index_places_on_country_id_and_population
Key Length 9
Ref const
Rows 81630
Extra Using where

Thanks,

Avishai

[Barry Hunter]

As I understand it (with mySQL at least) using large LIMIT offsets,
will always be quite slow, basically as it still as to run the query
and then basically disguard the early matches until the 'limit window'

You should perhaps look at other methods of 'paging', if you really
are paging though the results you know the last population on the last
page, so can do something like


SELECT * FROM `places` WHERE (`places`.`country_id` = 'BR') and
population < 10000 ORDER BY places.population DESC LIMIT 10

its not perfect, but often you may be able to sacrifice accuracy for speed.

The other option is to look at something like SphinxSearch - its a
full-text engine, but would probably manage this quite well, as it can
do sorting and grouping much like mysql. Note its not a database, so
you would still need to use it in conjunction with (say) mysql. I
havent used it with such large offsets, but suspect it might be better
than venila mysql. I have the geonames dump indexed in sphinx, perhaps
I could actully give it a try!




2008/12/23 Avishai <avisha...@gmail.com>:

--
Barry

- www.nearby.org.uk - www.geograph.org.uk -

[Martin Sarsale]

How many rows does that query returns? Please paste a "show create
table places".

Also, plz note US is not that much bigger than Brazil :)
http://en.wikipedia.org/wiki/List_of_countries_and_outlying_territories_by_area

>
> Thanks,
>
> Avishai
>
> >
>



--
Martin Sarsale - mar...@malditainternet.com