*cough*partitioning*cough* On Thu, Feb 18, 2010 at 5:24 PM, Jerry Schwartz <jschwa...@the-infoshop.com>wrote:
> From: Vikram A [mailto:vikkiatb...@yahoo.in] > Sent: Wednesday, February 17, 2010 11:41 PM > To: Jerry Schwartz > Cc: MY SQL Mailing list > Subject: Re: how things get messed up > > > > Dear Jerry Schwartz > > We have applications for colleges in India. The same idea of having single > table for manipulating students records. but we are not following archiving > concept. > > Ex stupersonal. and stuclass these tables are playing wide role in our > application. After 7 years now there are 9000 records[postgresql backend] > are there in the table. Because of this the entire application [ Fees, > attendance, exams etc] performance is getting down. For the remedy of this I > proposed this year wise architecture for our new version [mysql]. > > > > [JS] You have 9000 records? That should not slow down any application. I > must not understand you. > > > I have problem in year wise also, i have number of mutual related tables > for students such as stu_last_studies, stu_family_details, stu_address, > stu_extracurri and so on. If i go for year basisis i have to make all the > above tables also year basis. > Hence, I feel it difficult have such number of tables after few years. > > > > [JS] I did not mean that you should have tables for each year. I was > suggesting that you have tables for recent data and tables for archived > data. > > > As you said the archive system, can you the idea about the archive > system[If needed i will give the table structures]. > > > > [JS] This is best described with a picture. Here is a small example of what > I meant: > > > > `student_master_table` (all years) > > /\ > > / \ > > `grades_current` `grades_archive` > > | / > > `class_master_table` > > > > The structures of the two grades tables should be almost the same, > something like > > > > grade_id <autoincrement in grades_current only> > > student_id <index> > > class_id <index> > > class_start_date > > grade_received > > > > You would add new grade records to the `grades_current` table. > > > > Now, suppose that you don’t usually need data more than five years old. > Once a year you would run these queries: > > > > INSERT INTO `grades_archive` SELECT * FROM `grades_current` WHERE > `class_start_date` < YEAR(DATE_SUB(NOW(), INTERVAL 4 YEAR)); > > DELETE FROM `grades_current` WHERE `class_start_date` < > YEAR(DATE_SUB(NOW(), INTERVAL 4 YEAR)); > > > > That would keep the `grades_current` table small. If you want to find a > student’s recent grade history, you would use a query like > > > > SELECT * FROM `grades_current` WHERE `student_id` = 12345; > > > > If you decide that you need a student’s complete history, you could do > > > > SELECT * FROM `grades_current` WHERE `student_id` = 12345 UNION ALL SELECT > * FROM `grades_archive` WHERE `student_id` = 12345; > > > > That is a quick outline of what I was saying. > > > > I don’t know how big your database is, so I can’t begin to guess whether or > not this is necessary. On my desktop computer, where I do my testing, I have > two tables: one has about 104000 records, the other has about 200000 > records. The query > > > > SELECT `prod`.`prod_num`, `prod_price`.`prod_price_del_format`, > `prod_price`.`prod_price_end_price` FROM `prod` JOIN `prod_price` ON > `prod`.`prod_id` = `prod_price`.`prod_id` WHERE `prod`.`prod_num` = 40967; > > > > took .70 seconds. Repeating the same query with different values of > `prod_num` gave increasingly faster results, showing that caching is working > as expected: after three such queries, the response time was .14 seconds. > > > > I understand that schools in India can be very, very big; so perhaps you > need an archive scheme such as the one I described. In fact, it might be > useful to extend this whole concept to using an archive database, rather > than archive tables within the same database. The database engine wouldn’t > really care, but since the archive database wouldn’t change very often you > wouldn’t have to back it up very often, either. > > > > Regards, > > > > Jerry Schwartz > > The Infoshop by Global Information Incorporated > > 195 Farmington Ave. > > Farmington, CT 06032 > > > > 860.674.8796 / FAX: 860.674.8341 > > > > www.the-infoshop.com > > > > > > > It will be grate help to me. > > Thank you > > VIKRAM A > > > > _____ > > From: Jerry Schwartz <jschwa...@the-infoshop.com> > To: Vikram A <vikkiatb...@yahoo.in>; Johan De Meersman <vegiv...@tuxera.be > > > Cc: MY SQL Mailing list <mysql@lists.mysql.com> > Sent: Tue, 16 February, 2010 9:32:22 PM > Subject: RE: how things get messed up > > >-----Original Message----- > >From: Vikram A [mailto:vikkiatb...@yahoo.in] > >Sent: Friday, February 12, 2010 4:13 AM > >To: Johan De Meersman > >Cc: MY SQL Mailing list > >Subject: Re: how things get messed up > > > >Sir, > > > >Thanks for your suggestion, > >I will go for blob storage, because our application will maintain the data > on > >yearly basis[stupersonal2008, stupersonal2009 etc.]. So i feel we may not > >face > >such kind of performance issue in our application. > > > [JS] It sounds like you are planning to have one table per year. Regardless > of > where you put your blobs, I think that is a bad idea from a design > standpoint. > It will make it harder to find historical information. > > If your database is relatively small, then I'd just keep everything in one > table. If it is big, then roll data that is five years old into an archive > table. That will give you only two places, and an easy-to-follow rule to > tell > you where to look. > > Regards, > > Jerry Schwartz > The Infoshop by Global Information Incorporated > 195 Farmington Ave. > Farmington, CT 06032 > > 860.674.8796 / FAX: 860.674.8341 > > www.the-infoshop.com > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=vikkiatb...@yahoo.in > > > > > > _____ > > Your Mail works best with the New Yahoo Optimized IE8. Get it NOW! < > http://in.rd.yahoo.com/tagline_ie8_new/*http:/downloads.yahoo.com/in/internetexplorer/> > . > > -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
