help with making SQL query efficient in MySql

Question

I have 30 tables, each representing a different neighborhood. Each table holds real estate listing with a "Price", "Number Of Rooms", "Square Feet" etc columns.

The end user would be able to choose as many neighborhoods as he likes with the option to screen out results such as "At least 5 Rooms", "Below 250k" etc and sort the results by "Lowest Price", "Time Submitted", you get the point.

Now I'm a programmer not a DBMS guy. I've search the web but feel that trying to build the query one step at a time would be the wrong approach without some guidance on what to avoid.

I would love to hear and learn from the StackOverflow community on best approaches with this one. Please help me sort this up.

EDIT: i'm currently using MyISAM

Answer 1

You should not have 30 tables. Normalize your schema:

NEIGHBORHOOD
ID, Name

PROPERTY
ID, NeighborhoodID, Name, Price, Rooms, SquareFeet

Then you can join these together:

SELECT n.Name AS Neighborhood, p.Name AS Property, Price, Rooms, SquareFeet
FROM Property AS p
INNER JOIN Neighborhood AS n ON h.NeighborhoodID = p.ID
WHERE p.NeighborhoodID = X

Then you may need indexes on the tables as the data grows.

Answer 2

The best way is to change your db model, get rid of 30 tables, and put everything in one table. With your current model, I don't see any other ways but create a huge union (you can put it into a view, and query this view).

Answer 3

You should start modifying your database model. Creating 30 tables for storing the same data (real state information) is not adequate. Try to put all the data in a single table adding a column that indicates the neighborhood. This neighborhood could point to another table with the name, description, ... of the neighborhood. Then you can query a single table to search across all neighborhoods and optionally filtrate the neighborhood the user want to search for.