Mysql – Zip based search in thesql.

javaMySQLPHPphpmyadmin

I want to implement a zip based search in mysql database, I am populating the location column in my database based on a drop-down where in I have predefined zip codes, now if a user wants to search for a commodity which is in the radius of 75 miles of his present location, I want ideas as how and which is the best way to accomplish this. I am new to mysql so a detailed answer would be much appreciated. I would be much glad if I am given some example too.

Best Answer

There are a couple of ways you could do this

a. Geocode your postcodes and calculate the distance between the 2 points (or use an existing API to do this). There are several places you can geocode small quantities of addresses for free, or you can purchase databases. Note that this method is not entirely trivial, but is probably the best for the long term.

You can get Geocoding done via Google at https://developers.google.com/maps/documentation/geocoding/

Then, to calculate the distance, use https://developers.google.com/maps/documentation/distancematrix/

b. Manually set which zip codes are bordering other zip codes in the database. You say you are supplying predefined zip codes so this may be a simpler solution (though for a large number of zip codes it would be costly) The idea is that you have a new table zip_neighbours as follows

 Zip code,     neighbour
  100,       101, 102, 103
  101,       100, 102, 104
  102,       100, 101, 104

This wont give an accurate '75 miles' boundary, but can be used as a pretty good indication. When a user wants to include nearby zip codes you lookup the neighbours in this table and include them in the search.

Related Solutions

MySQL: self-administration for multiple users

I think using a MySQL gui tool like SQLyog would solve your problem. It has User manager tool where you can set privilege to different users.

enter image description here

MySQL: logging queries which would execute without using indexes

I'm not really sure why you expected log_queries_not_using_indexes=1 to not log queries that didn't use an index. The idea of the setting is to see which queries are getting run on your server that could be ideal candidates for optimization. For your example:

SELECT id_test from test where id_test != 69

Unless you change the requirements of the query, there's not much you can do. Of course it's faster to do a full scan and return all rows that don't match that id_test value.

As for ideas on how to approach analysis:

You can set min_examined_row_limit to a higher value. If, for example, you want to exclude queries that examine less than 5,000 rows, set this variable to 5000. This is useful to reduce the 'noise' in the slow query log.
You can use a tool called pt-query-digest with the --review option to review your slow queries. The tool allows you to store your review history so you don't repeat work on the same query during analysis:

It won’t show you queries you’ve already reviewed. A query is considered to be already reviewed if you’ve set a value for the reviewed_by column. (If you want to see queries you’ve already reviewed, use the --report-all option.)

It also has the added benefit of storing a history of the queries that get logged in your slow query. Who wouldn't love historical evidence of improving your queries?

Best Answer

Related Solutions

MySQL: self-administration for multiple users

MySQL: logging queries which would execute without using indexes

Related Question