Re: speed up query

[Shabbir Ahmed]

* 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).