更好的想法如何从数据库中获取大量的MySql数据

Question

I have a big database with a lot of adresses, for exampte like this (here as json):

{"id":"1","desc":"Frankie Johnnie & Luigo Too","address":"939 W El Camino Real, Mountain View, CA","lat":"37.386337","lng":"-122.085823"}

I get this data with a simple MySql - Statement:

'Select * from datatable'

And with the answer of this I create the json statements. The Problem is that every data get out of the table and when the database gets bigge, I think that the request takes some time. I don't have any idea how to better select the data. I Need all Data out of teh db to create a map.

Is there a better solution to create not so much traffic? Or is this request not so intensive and don't create so much traffic?

Thanks!

Answer 1

If you create a map you should only get data to draw onto the map, try something like:

SELECT * FROM datatable 
    WHERE lat > 20 
    AND lat < 40
    AND lng > -140
    AND lng < -100;

Of course this is just an example, you should calculate latitude and longitude limits based on the area you are presenting on the map. Make sure to take into account wraparounds and if you're showing a big enough area of the globe than a square might not be the best match, if you google around there are multiple ways to get proper geodata limitations for anything you want.

If this is not enough you could also narrow it down based on other principles, that is a partial match on description or address.

SELECT * FROM datatable 
    WHERE lat > 20 
    AND lat < 40
    AND lng > -140
    AND lng < -100
    AND desc LIKE '%Frankie%';