473,698 Members | 2,690 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

MySQL design question

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.
Jan 24 '08 #1
3 1273
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/
Jan 24 '08 #2
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.
js*******@attgl obal.net
=============== ===

Jan 24 '08 #3
..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
Jan 25 '08 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

19
8695
by: Westcoast Sheri | last post by:
To keep track of how many fruits my visitors buy, I use a mySQL database (2 columns: "fruit" and "quantity")....so can we make these following mySQL queries work somehow? (visitor buys 5 apples): replace into fruit_database set fruit = 'apple' , quantity = quantity + 5; (visitor buys 7 apples): replace into fruit_database set fruit = 'apple' , quantity = quantity +
133
9056
by: jonathan | last post by:
hey all, I realize that this question might pop up from time to time, but I haven't seen it a while and things might of changed, so - Right now (July 2004) how does mysql stand up in comparison to oracle? We are seriously considering migrating our multi-processor oracle system to mysql to save on licensing costs, and would need several features that mysql may or may not have:
2
1556
by: kimshapiro100 | last post by:
Question on PhP, MySQL I am thinking of a consumer internet business for which I will have to have a database driven site built. I am thinking of using PhP, MySQL as the main technologies amd Linux hosting( and some Dreamweaver). My question is:
175
11425
by: Sai Hertz And Control Systems | last post by:
Dear all, Their was a huge rore about MySQL recently for something in java functions now theirs one more http://www.mysql.com/doc/en/News-5.0.x.html Does this concern anyone. What I think is PostgreSQL would have less USP's (Uniqe Selling Points
39
8402
by: Mairhtin O'Feannag | last post by:
Hello, I have a client (customer) who asked the question : "Why would I buy and use UDB, when MySql is free?" I had to say I was stunned. I have no experience with MySql, so I was left sort of stammering and sputtering, and managed to pull out something I heard a couple of years back - that there was no real transaction safety in MySql. In flight transactions could be lost.
2
1900
by: moller | last post by:
Im looking in to the possibility of moving from mySQL to an access database. My reasons are: (1) Database is single user. (2) Database local on users PC. (3) Database has only 8 tables where 4 are filled at database creation with aprox 20 rows each and are never added to after that. (4) Database grows with ca 6000 rows/week. (5) No data is ever deleted, exept... (6)
2
269
by: ME | last post by:
I am getting: "ERROR SQLBindParameter not used for all parameters" when attempting to run the following code: Private Sub DataGrid1_UpdateCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DataGrid1.UpdateCommand
39
3215
by: windandwaves | last post by:
Hi Folk I have to store up to eight boolean bits of information about an item in my database. e.g. with restaurant drive-through facility yellow windows
110
10579
by: alf | last post by:
Hi, is it possible that due to OS crash or mysql itself crash or some e.g. SCSI failure to lose all the data stored in the table (let's say million of 1KB rows). In other words what is the worst case scenario for MyISAM backend? Also is it possible to not to lose data but get them corrupted?
2
3428
by: Roopesh | last post by:
Hi, In my mod_python project I am using mysql as the database. There is table card in which unique cards are stored. When a user request comes he has to get a unique card. In this situation I want to use LOCK with which I can prevent other users accessing the table. I tried excuting "LOCK" command of mysql through python code, but it is not locking the database. Any ideas why this isn't working and how can I do the same. //python code...
0
8609
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
8901
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8871
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7739
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6528
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5862
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4371
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4622
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2336
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.