Thanks very much for your reply.
I'm using IIS 6 with Windows 2003 Server Standard and MySQL 5.
My main problem is one web page I have written in particular. It needs to
display anything from 1 to 200 records/rows from the MySQL database. When
you update this page (pressing submit) it then needs to update every single
record on each of the 200 lines. Also the ASP file is quite large because
the web page has about 3,000 lines of code - about 60K. It works fine with
a few hundred thousand records but when I go over the million mark it can
take 30 seconds to submit the page which is too long.
So you would recommend multiple tables rather than multiple databases?
Will using MERGE on 1,000 tables be quicker than keeping everything in one
table?
Thanks in advance.
James Mackie
Appiam Ltd
Developer
www.appiam.com
james@stripped
07884 494 333
----- Original Message -----
From: "John Paul Ashenfelter" <john.paul.ashenfelter@stripped>
To: "James - Developer" <james@stripped>
Cc: <win32@stripped>
Sent: Tuesday, November 30, 2004 10:06 PM
Subject: Re: Large DB Problem.
>I think it's *far* more likely you need to spend some time on database
> optimzation. 10m rows is a lot, but not that big -- there are folks
> using MySQL for with databases over a *terabyte*. You are going to run
> into issues as individual tables get above 2 terabytes, but I'm
> guessing you're probably only in the 10-100GB range for the whole
> database.
>
> That said, you might look at MERGE tables -- that way you can
> partiotion the table by company, for example, but still have access to
> the the whole thing through a merge table.
>
> Of course all this is speculation since the email is pretty vague :)
> So are there particularly slow queries? And which version of
> everything? And how are you connecting? etc, etc.
>
>
> On Tue, 30 Nov 2004 21:56:39 -0000, James - Developer <james@stripped>
> wrote:
>> Hi, wondering if anyone could please help.
>>
>> I have been writing an ASP/MySQL application over the last 12 months. In
>> the last month the company who have paid me have brought 8 other
>> companies which do the same as what they do. So, as you can imagine I am
>> concerned. I have a main database with around 20 tables. The busiest
>> table / the one with potential to become the largest contains around 15
>> columns. I have calculated that within 6 months, this table could quite
>> easily have 10,000,000 (10m) records or worse case 200,000,000 (200m)
>> records. So I have run a test and carried out a bulk insert of 5,000,000
>> (5m) records and as you can imagine, accessing this over the web is
>> very/too slow to be practical.
>>
>> So, I need to split the database. My initial thought was to split the
>> database by adding an extra table for every new client who joins and it
>> is predicted to be 1,000 clients per year which is the same as the
>> previous years. I then thought this would be the same as my initial
>> method because MySQL has to first access one of the 1,000 tables and then
>> one of the thousands of records which would not make any difference.
>>
>> My final thought and this is my question: I have decided to add a
>> completely new database for every client. This is not a problem because
>> the data does not need to be relational between clients. This means
>> having 1,000 databases/folders and I will use a DSN string with the
>> 'database=;' value to equal the relevant client/database name. My
>> conception is that the OS (Windows) will access any one of the 1,000
>> databases quicker than MySQL can and then MySQL will find the details
>> from the relevant database which will only be 1,000th the size of my
>> initial idea.....
>>
>> Does anybody know if this is correct?
>>
>> I certainly can't have one large table because the database will be too
>> slow.
>>
>> Thanks very much in advance.
>>
>> James Mackie
>> Appiam Ltd
>> Developer
>> www.appiam.com
>> james@stripped
>>
>>
>
>
> --
> John Paul Ashenfelter
> CTO/Transitionpoint
