1

I have a huge (100+ Gig of data, ~1 billion rows) table on which I need to perform SELECT queries that are very fast for recent data as well as queries for older data where the speed is unimportant. I'm thinking about creating a new table for INSERTing new data and performing fast SELECT queries that will be copied every hour or so into the large table and the records will be deleted. Is it a good idea or should I rather use partitioning?

Martin
  • 22,212
  • 11
  • 70
  • 132
pedmillon
  • 143
  • 3
  • 13
  • by 100+G you mean a table having over 100GB of data? How many records are that? – juergen d Jun 30 '16 at 14:26
  • That's right, it has around 1 billion records. – pedmillon Jun 30 '16 at 14:36
  • 1
    You might have a strong use case for partitioning. However, it is also dependent on the schema of your table. How will you divide the tables? What do your indexes look like? What is your threshold for a "fast" query? – SArnab Jun 30 '16 at 15:15
  • Throw money at the problem. Nothing silences a problem for a while than throwing money at it. Speaking of which, I need to check in on my son. – Drew Jun 30 '16 at 20:56
  • We cannot discuss this problem without seeing `SHOW CREATE TABLE` and the 'query'. – Rick James Jul 07 '16 at 01:32
  • Have a read of the accepted answer and comments here: http://stackoverflow.com/questions/4419499/mysql-and-nosql-help-me-to-choose-the-right-one/4421601#4421601 – Jon Black Aug 07 '16 at 08:31

0 Answers0