NYCPHP Meetup

[nycphp-talk] MySQL & 2.5 million rows!

Jim Hendricks jim at bizcomputinginc.com
Fri Apr 11 12:19:22 EDT 2003


One word of warning for the table renaming, it relies on specifics of the
SQL Server in use, ie. mySQL.  This would mean that If you were to change
out your DB Server in the future, you are not assured that your app will
still work without tweaks.  If no plans are to ever change the SQL Server
then the renameing is probably the simplies solution.

Jim
______________________________________________________________
Jim Hendricks, President, Biz Computing, Inc
Phone:  (201) 599-9380     Email: jim at bizcomputinginc.com
Web: www.bizcomputinginc.com
Snail:  Jim Hendricks,  Biz Computing, Inc.,  255 McKinley Ave, New Milford,
NJ 07646
______________________________________________________________

----- Original Message -----
From: <jsiegel1 at optonline.net>
To: "NYPHP Talk" <talk at nyphp.org>
Sent: Friday, April 11, 2003 12:15 PM
Subject: Re: [nycphp-talk] MySQL & 2.5 million rows!


> I've gotten a lot of great suggestions (thanks so much for the quick
response...I hope to get to one of the NYPHP meetings some day) and I
definitely *LIKE* the table renaming idea.
>
> Now...of course...I've got to make a choice as to which method to use!!
:)
>
> Jeff
>
> ----- Original Message -----
> From: Andrew Yochum <andrew at digitalpulp.com>
> Date: Friday, April 11, 2003 11:46 am
> Subject: Re: [nycphp-talk] MySQL & 2.5 million rows!
>
> > A similar suggestion to the one below is to rename the tables to
> > do the move.
> > The table naming then can remain human readable at any given time.
> >
> > Andrew
> >
> > On Fri, Apr 11, 2003 at 11:34:18AM -0400, Jim Hendricks wrote:
> > > You may want to save time and not move the data around at all.
> > >
> > > The idea would be to have 3 tables all structured the same each
> > named> uniquely.  Start by loading up table 1, then in the
> > application point the
> > > data to table1.  When doing the next import, detect that table 1
> > contains> the live data so use table2 to import the data.  If the
> > data imports
> > > correctly, change the app to point to table2, table1 now becomes
> > your old
> > > data.  Next import table 3 becomes your import, then live, table
> > 2 becomes
> > > old, table 1 gets emptied ready for next import.  Next import, 1
> > becomes> import then live, table 3 becomes old, table 2 gets
> > emptied ready for next
> > > import.
> > >
> > > Hope you get the idea.
> > >
> > > Jim
> > > ______________________________________________________________
> > > Jim Hendricks, President, Biz Computing, Inc
> > > Phone:  (201) 599-9380     Email: jim at bizcomputinginc.com
> > > Web: www.bizcomputinginc.com
> > > Snail:  Jim Hendricks,  Biz Computing, Inc.,  255 McKinley Ave,
> > New Milford,
> > > NJ 07646
> > > ______________________________________________________________
> > >
> > > ----- Original Message -----
> > > From: <jsiegel1 at optonline.net>
> > > To: "NYPHP Talk" <talk at nyphp.org>
> > > Sent: Friday, April 11, 2003 10:24 AM
> > > Subject: [nycphp-talk] MySQL & 2.5 million rows!
> > >
> > >
> > > > You read the subject line correctly!!! I'm loading 2.5 million
> > rows from
> > > an ASCII file into a MySQL database. So...here's a little
> > background on what
> > > I've done and then a question. (Please keep in mind I'm a Php/MySQL
> > > newbie...though I'm learnin' fast!!)
> > > >
> > > > I created three tables - data_new, data_old, data_live. The
> > Ascii file
> > > gets read, line by line, and inserted into data_new. When it's
> > completed and
> > > there are no glitches (i.e., no problem with the Ascii file), I
> > want to move
> > > the data from data_live to data_old and then move the new data
> > from data_new
> > > to data_live. So...the question...is there a fast way to move
> > the data from
> > > one MySQL table to another (from data_new to data_live) other
> > than walking
> > > through data_new row by row...creating an Insert statement on
> > the fly...and
> > > then inserting the row into data_live?
> > > >
> > > > BTW, in case you are wondering why there are three different
> > tables, I
> > > felt that this was a better way  than my client's present system
> > which> simply wipes out the live data and then reads in the Ascii
> > file. If there is
> > > a glitch then they have to empty the table and reload the Ascii.
> > Doing it
> > > this way, if they need to go back to the old data, I would move
> > it data from
> > > data_old to data_live.
> > > >
> > > > Jeff
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >
> > >
> > >
> >
> > --
> > Andrew Yochum
> > Digital Pulp, Inc.
> > 212.679.0676x255
> > andrew at digitalpulp.com
> >
> >
> >
> >
> >
> >
>
>
>
> --- Unsubscribe at http://nyphp.org/list/ ---
>
>
>
>




More information about the talk mailing list