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

Is there any reason why you have 30 tables, which as far as we have been told cary identical types of data? — Peter M
– Peter M, Commented Mar 24, 2011 at 21:13
Yes because i set up a cron job to output data from them to an xml every once in a while and i want to scale so that was the best option for me, instead of every time a user visits the neighborhood page to query WHERE neighborhood = 'myhood". — Tom
– Tom, Commented Mar 24, 2011 at 21:17
Using indexes will solve the scaling problem for you, even if you listed every property in the world. — btilly
– btilly, Commented Mar 25, 2011 at 0:25

btilly · Accepted Answer · 2011-03-25 00:23:29Z

3

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.

edited Mar 25, 2011 at 0:23

btilly

47.8k3 gold badges69 silver badges101 bronze badges

answered Mar 24, 2011 at 21:16

Dustin Laine

38.7k10 gold badges90 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alin P. Over a year ago

Real-estate, not hotels :). You can put Property for the table name.

Tom Over a year ago

what if i want to limit the results each time say to 6? does the LIMIT option helps? or does it render everything and only display 6 results?

Dustin Laine Over a year ago

The LIMIT option will only return the amount of results specified. The query optimizer will not render then return it will include the limit in is analysis and return the top results in the most efficient manner it can.

David Oliván · Accepted Answer · 2011-03-24 21:16:48Z

0

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.

answered Mar 24, 2011 at 21:16

David Oliván

2,7251 gold badge20 silver badges26 bronze badges

Comments

a1ex07 · Accepted Answer · 2011-03-24 21:19:11Z

0

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).

answered Mar 24, 2011 at 21:19

a1ex07

37.4k12 gold badges93 silver badges103 bronze badges

Collectives™ on Stack Overflow

help with making SQL query efficient in MySql

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related