Posted by The Natural Philosopher on 01/24/08 11:55

Neeper wrote:
> I'm creating a classifieds application for multiple cities (about
> 20-50 cities). I'm not sure whether to use a single table to store for
> all cities' items or break each one out into a seperate table for each
> city.
>
> I know a seperate tables will be faster for searches because there
> will be less records but in terms of maintenance it gets a little
> messy and hectic as the list of cities will grow.
>
> I guess it all comes down to is, the number of records I would have.
> I'm not sure how many records it takes before MySQL starts to slow
> down.
>
>
> Please give me your thoughts.
>

Indexing takes care of a lot of these speed issues.

The way I would do this, is to have a table of cities, and a separate
table of classifieds, each one of which has a city field pointing to the
city in question (and maybe other fields pointing to classified category
and so on, as well as expiry date etc)

Then index everything..MySQL will create all the sub index tables so
that a query like.

(Select * from classifieds where city='mycity' and category='mycategory')

will be fast.



>
> Thanks.

[Back to original message]