MySQL design question

Neeper wrote:

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

Separate tables will work at a snail's pace if you want to do cross-city
searches.

Keep them in one table. Index any columns that you plan on searching or
sorting by.

> I'm not sure how many records it takes before MySQL starts to slow
> down.

I dunno -- millions? Depends on many factors.

Just make sure that you use PDO or some other cross-database programming
tool, and avoid MySQL-specific syntax nuances. That way, you can easily
port the application to another database (perhaps even a database cluster)
if your data grows out of control.

--
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 24 days, 21:06.]

CSS to HTML Compiler
http://tobyinkster.co.uk/blog/2008/01/22/css-compile/
 >> Stay informed about: MySQL design question 

On 24 Jan, 09:39, Erwin Moller
wrote:
> Neeper wrote:
> > I'm creating an 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.
>
> Hi,
>
> If the application is classified, as you wrote in your first post, you
> might also consider using different databases for each city, with
> different username/passwords.
> If you don't, you should make very sure city1 cannot access data for
> city2, eg by doing getname.php?cityid=2 or something like that.
>
> To your question, if you use an index on the relevant columns on the
> table, the queries are probably very fast, IF you use that indexed
> column in your where-clause.
>
> Regards,
> Erwin Moller
>
>
>
> > Thanks.

Whilst this subject has nothign to do with php, I think you have the
wrong idea about the context in which "classified" is being used.

It is calssified as in adverts being listed in categories (the
classes) rather than classified as in "top secret".
 >> Stay informed about: MySQL design question 

On 24 Jan, 12:26, Erwin Moller
wrote:
> Captain Paralytic wrote:
> > On 24 Jan, 09:39, Erwin Moller
> > wrote:
> >> Neeper wrote:
> >>> I'm creating an 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.
> >> Hi,
>
> >> If the application is classified, as you wrote in your first post, you
> >> might also consider using different databases for each city, with
> >> different username/passwords.
> >> If you don't, you should make very sure city1 cannot access data for
> >> city2, eg by doing getname.php?cityid=2 or something like that.
>
> >> To your question, if you use an index on the relevant columns on the
> >> table, the queries are probably very fast, IF you use that indexed
> >> column in your where-clause.
>
> >> Regards,
> >> Erwin Moller
>
> >>> Thanks.
>
> > Whilst this subject has nothign to do with php, I think you have the
> > wrong idea about the context in which "classified" is being used.
>
> > It is calssified as in adverts being listed in categories (the
> > classes) rather than classified as in "top secret".
>
> Oh yes, you might be right.
> Categorized might have been a clearer word.
>
> Erwin

True, but they are known as "classified ads", in both newspapers and
on things like ebay.
 >> Stay informed about: MySQL design question 

Neeper wrote:
> I'm creating an 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.
>

You'll get much better answers from database experts over in
comp.databases.mysql.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex.RemoveThis@attglobal.net
==================
 >> Stay informed about: MySQL design question 

Captain Paralytic wrote:
> On 24 Jan, 09:39, Erwin Moller
> wrote:
>> Neeper wrote:
>>> I'm creating an 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.
>> Hi,
>>
>> If the application is classified, as you wrote in your first post, you
>> might also consider using different databases for each city, with
>> different username/passwords.
>> If you don't, you should make very sure city1 cannot access data for
>> city2, eg by doing getname.php?cityid=2 or something like that.
>>
>> To your question, if you use an index on the relevant columns on the
>> table, the queries are probably very fast, IF you use that indexed
>> column in your where-clause.
>>
>> Regards,
>> Erwin Moller
>>
>>
>>
>>> Thanks.
>
> Whilst this subject has nothign to do with php, I think you have the
> wrong idea about the context in which "classified" is being used.
>
> It is calssified as in adverts being listed in categories (the
> classes) rather than classified as in "top secret".

Oh yes, you might be right.
Categorized might have been a clearer word.

Erwin
 >> Stay informed about: MySQL design question 

..oO(Neeper)

>I'm creating an 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.

One table.

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

There won't be much of a difference if the tables are properly indexed.

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

Millions.

Micha
 >> Stay informed about: MySQL design question