MySQL database design

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.


Thanks.

Re: MySQL database design

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

For MySQL questions, try comp.lang.mysql. That's what it's there for.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Re: MySQL database design

Jerry Stuckle wrote:
> 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.
>>
>>
>> Thanks.
>>
>
> For MySQL questions, try comp.lang.mysql. That's what it's there for.
>

Uh, that should be comp.databases.mysql :-)

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Re: MySQL database design

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.

Re: MySQL database design

The Natural Philosopher wrote:

> Then index everything..

If you index *everything* then you slow down INSERTs. It's better to index
only the columns that you know will benefit from it. (If you find that you
need more indexes, it's easy enough to add them later.)

--
Toby A Inkster BSc (Hons) ARCS
[Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
[OS: Linux 2.6.17.14-mm-desktop-9mdvsmp, up 25 days, 23 min.]

CSS to HTML Compiler
http://tobyinkster.co.uk/blog/2008/01/22/css-compile/

Re: MySQL database design

Toby A Inkster wrote:
> The Natural Philosopher wrote:
>
>> Then index everything..
>
> If you index *everything* then you slow down INSERTs.

Well you know what I meant. Everything relevant to the search, but a
classified is a classic instance of post one, read many, type
applications. So a small hit in posting is no biug deal.

> It's better to index
> only the columns that you know will benefit from it. (If you find that you
> need more indexes, it's easy enough to add them later.)
>

Oh yes. The beauty of databases.


We had an old informix systyem once we were developing management
reports for. The first report took 11 hours to complete. The second,
rewritten in syntax to do the correct bits FIRST, took 15 minutes.
Adding indices got it down to around 15 seconds IIRC.

Re: MySQL database design

On Thu, 24 Jan 2008 06:00:31 +0000, 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.

Read up on "Database Normalization" and your question will answer itself.

--
I told you this was going to happen.