My System database has got a very large amount of data and some tables are really getting very big. I was looking for a way such as when the system will query on those large tables, it will ignore the historical data and will start looking from the index that I will set. Something like I have 20000 rows but I will set 15000 index as the starting point, so the query will think 15001 is the first index and will start operation. How can I achieve this? I do not want to delete the historical data from my tables.

  • Simply include the correct WHERE clause, select .. from the_table where id > 15000 – a_horse_with_no_name Mar 9 '20 at 8:59
  • How big are your big tables? Are you sure you need to do this? – dbdemon Mar 9 '20 at 9:01

In most circumstances with optimal queries and good index choices this should not be necessary at all. It would only make a difference when the database engine is scanning the whole table (or in the cases where LIMIT is in effect, scanning the table until matches are found) and you should be trying to eliminate this sort of query plan anyway.

Indexes are usually implemented as tree structures, usually some variety of b-tree. A lookup for a single value (so a single row) in a binary tree has a complexity of the order of Log2 N so for 1,000,000 rows you can expect "up to 20" page reads (and most of those pages should already be in RAM so that isn't disk reads/seeks). For 1,000,000,000 rows in the table this only grows to "up to 30" page reads. This effect is massive compared to the effect of just ignoring the old rows in your table. The matter is the same for range queries: the initial seek on the index is the same, and from there the reading is sequential with respect to the tree structure anyway.

So instead of trying to ignore old data artificially, instead have good index and query design and it will not be an issue.

Obviously this is a generalism, there obviously are some queries that simply have to scan large chunks of the table, even all of it, that can't be further optimised, and no end of indexing will help the query planner, but these are genuinely rare. Some database systems have features for dealing with this sort of issue over massive tables: for instance MS SQL Server, postgres, and others, support filtered indexes (called partial indexes in postgres' terminology) which can exactly implement what you are asking for in your question, but that is probably not what you actually need at this point. You might also look at data partitioning but again I suspect that is not what you need, partitioning is more about managing the storage of large data than about searching it more effectively.

  • Is there any tool/software to perform data archiving automatically? – Sayed Uz Zaman Mar 9 '20 at 15:42

Basically, I can see two options:

  1. You can create a new, empty database with the same schema as your current database (on the same instance/installation of MySQL) with auto_increment=1 for each table.

  2. You can create a backup of your database, then truncate all the tables, i.e. delete the data using TRUNCATE t1; TRUNCATE t2; TRUNCATE t3; ... TRUNCATE tn;. This will automatically set auto_increment=1 for each table.

  • if I set the auto increment to zero then how it will perform the insert operation? – Sayed Uz Zaman Mar 9 '20 at 8:58
  • @user269156 Sorry, it's actually auto_increment=1. It will use 1 as the first PK value, 2 as the second PK value and so on, assuming it's a PK that uses auto_increment. – dbdemon Mar 9 '20 at 9:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.