From: Johan De Meersman Date: February 18 2010 4:35pm Subject: Re: how things get messed up List-Archive: http://lists.mysql.com/mysql/220740 Message-Id: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary=0016e6498a5c53a501047fe28f77 --0016e6498a5c53a501047fe28f77 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: quoted-printable *cough*partitioning*cough* On Thu, Feb 18, 2010 at 5:24 PM, Jerry Schwartz wrote: > From: Vikram A [mailto:vikkiatbipl@stripped] > 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 singl= e > table for manipulating students records. but we are not following archivi= ng > 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 thi= s 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 wh= at > 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 > > student_id > > class_id > > class_start_date > > grade_received > > > > You would add new grade records to the `grades_current` table. > > > > Now, suppose that you don=92t 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=92s recent grade history, you would use a query like > > > > SELECT * FROM `grades_current` WHERE `student_id` =3D 12345; > > > > If you decide that you need a student=92s complete history, you could do > > > > SELECT * FROM `grades_current` WHERE `student_id` =3D 12345 UNION ALL SEL= ECT > * FROM `grades_archive` WHERE `student_id` =3D 12345; > > > > That is a quick outline of what I was saying. > > > > I don=92t know how big your database is, so I can=92t begin to guess whet= her or > not this is necessary. On my desktop computer, where I do my testing, I h= ave > 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` =3D `prod_price`.`prod_id` WHERE `prod`.`prod_num` =3D 4= 0967; > > > > took .70 seconds. Repeating the same query with different values of > `prod_num` gave increasingly faster results, showing that caching is work= ing > 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= =92t > really care, but since the archive database wouldn=92t change very often = you > wouldn=92t 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 > To: Vikram A ; Johan De Meersman > > Cc: MY SQL Mailing list > Sent: Tue, 16 February, 2010 9:32:22 PM > Subject: RE: how things get messed up > > >-----Original Message----- > >From: Vikram A [mailto:vikkiatbipl@stripped] > >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 da= ta > on > >yearly basis[stupersonal2008, stupersonal2009 etc.]. So i feel we may no= t > >face > >such kind of performance issue in our application. > > > [JS] It sounds like you are planning to have one table per year. Regardle= ss > 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 on= e > table. If it is big, then roll data that is five years old into an archiv= e > 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=3Dvikkiatbipl@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/inte= rnetexplorer/> > . > > --=20 Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel --0016e6498a5c53a501047fe28f77--