Question

I have a rather large table with 150,000 + records. It has a lot of fields like country_id, region_id, city_id, latitude, longitude, postal_code, to name just a few.

我需要根据纬度和长度从这个桌上挑选,别无选择。我要问:

SELECT * FROM `mytable` 
  WHERE `latitude` =  $latitude  
  AND `longitude` =  $longitude ;

现在,尽管我从这个桌子中挑选的唯一标准是自由度/长度,但我很想知道,如果增加更多的具体性,那么就象:

SELECT * FROM `mytable` 
  WHERE `city_id` =  320  
  AND `latitude` =  $latitude  
  AND `longitude` =  $longitude ;

对我来说,增加询问条件会加快速度,但与此同时,我通过首先确保所选择的所有记录都来自我知道居住在纬度和长度地带的特定城市,从而缩小了可能取得的成果。

记录总数可能大约为150k,但只有约10k来自该特定城市。

因此,这完全是明智的,还是我只是让问题更加费时?

Answer 1

总而言之,添加<代码>的条件 WHERE 你的询问中的条款不会对执行时间产生重大影响。但是,如果您在<代码>中提及的领域上加索引,则 WHERE 条款,你可以大大改进业绩。

So in your example, if there is not an index on city_id and you were to add that condition to your WHERE clause and create an index on that field, you would likely see a considerable performance improvement.

Answer 2

增加标准可以有两种办法,但通常更多的标准有助于业绩,特别是如果各栏有指数。

如果你有一个指数,包括城市——补贴和长期补贴,那么你的工作就会更好。

友情链接