MySQL Forums
Forum List  »  Performance

Re: speed up query
Posted by: Shabbir Ahmed
Date: April 30, 2010 08:21AM

* You will be inserting rows rapidly -- how rapidly? (The 9/sec you have should not be bad; are you now talking about 18/sec?)
8.1 million records per day.

* Does the data come in all at once (eg batch load once a day), or continually?
batch load (twice a day, before it was 4 times a day)

* How many months' worth of data can you hold on your disk? (This leads into at least two of the following questions.)
two years.

* Is deleting the old data part of the equation? Why? To keep from running out of disk? To speed up queries? (The overhead of deleting data may be worse than simply leaving it in the table.) (Partitioning on time, as you did, is the optimal way to delete "old" data. Separate tables but makes your code clumsier.)
i cant delete for less than 2 years

* Is the data readonly once it is inserted? (That is, no updates, deletes (except for purging the oldest), insertions of old data.)
yes read only (no deletes or updates)

* What are the SELECT type queries? Canned or ad hoc? Usually summary info over a date range? We have discussed one query; are there others? (Optimizing for one query may not help some other query.)
there are too many but the one i discussed was the one of the bulkier queries but ill share all of them soon,

* Some reason for InnoDB, not MyISAM? (There are benefits and drawbacks of each.)
innodb (I'll have to setup an H.A. for mysql (replication).

Options: ReplyQuote


Subject
Views
Written By
Posted
4140
April 18, 2010 06:42AM
1506
April 18, 2010 04:04PM
1587
April 18, 2010 05:40PM
1315
April 27, 2010 02:29PM
1455
April 27, 2010 07:59PM
1319
April 28, 2010 12:12AM
1576
April 28, 2010 06:22AM
1523
April 29, 2010 01:03AM
1377
April 29, 2010 02:17AM
1381
April 29, 2010 08:19AM
1385
April 29, 2010 09:18AM
1312
April 30, 2010 12:30AM
1564
April 30, 2010 07:47AM
Re: speed up query
1484
April 30, 2010 08:21AM
1398
April 30, 2010 10:08AM
1319
May 01, 2010 02:34AM
1333
May 01, 2010 09:54AM
1287
May 01, 2010 10:48AM
1415
May 01, 2010 02:20PM
1355
May 02, 2010 03:54AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.