I've got a MySQL table from the third-party application which has countless rows and just one index - the timestamp of every entry. Now I wish to perform some heavy self-joins and queries around the data using fields apart from the timestamp. Doing the query around the original table will bring the database to some crawl, adding indexes towards the table isn't a choice. Furthermore, I just have records which are more recent than a single week.

My current technique for doing the queries effectively is by using another table (aux_table) which has the required indexes. My questions are: Can there be a different way to perform the queries? and when not, How do you update the information within the indexed table effectively?

To date I've discovered two processes for upgrading aux_table:

  1. Truncate aux_table and place the preferred data in the original table. Not so efficient because all of the indexes should be re-crated.
  2. Look for the greatest timestamp in aux_table and place all records having a greater or equal timestamp in the original table. From time to time drop older records. Only copying records with greater timestamp results in dropped records (due to records with same timestamp which were placed in to the original table following the last update).

possibly:

give a trigger towards the large table to instantly also place in to the small table.

after that you can trim the little table for an appropriate time range at any time.