0

I have a huge database that keeps getting bigger. Query times are taking longer and longer (I'd like them to be under a second, they're at two and they're going to take longer). For scaleability, I'd like to split the table into separate, smaller tables based on what column state says. The database would then have 50 medium sized tables rather than one huge, slow table. Is there any way to do this with pure SQL queries (assuming the tables are pre-made)?

Osmium USA
  • 1,751
  • 19
  • 37
  • DBA can help more with this but I think you're after a concept called: partitioning http://dev.mysql.com/tech-resources/articles/partitioning.html or http://www.mysql.com/why-mysql/#en-0-31 for more info or http://stackoverflow.com/questions/14053363/mysql-partitioning-performance-increase-for-multiple-partitioned-tables-why – xQbert Oct 11 '13 at 18:03
  • 3
    Wait. Before talking about partitioning, let's understand with what we're dealing. How big is "*huge*"? Sometimes people come on here claiming that a few hundred thousand records is huge. Or even a million records. Or even ten million. That's not really huge, in the grand scheme of databases. In fact, that's really quite small-beer. If you're having problems scaling to that sort of order, then you likely haven't deployed a sensible indexing strategy. – eggyal Oct 11 '13 at 18:21
  • Agreed. I assumed author of the OP had used analysis tools and determined that re-write of SQL or addition/alter of indexes had already been considered, and still, at optimal performance, was not hitting desired times. – xQbert Oct 11 '13 at 18:25
  • He's on MySQL, 1 million records can be huge for it...it's probably scaling horribly. Osmium - I'd research / optimize your queries, splitting a table is really a last resort option. If you are doing more than simple selects, I'd recommend a move away from MySQL as well. – Twelfth Oct 11 '13 at 18:43
  • I'm talking just shy of 130,000,000 records. But the query still takes a bit to execute. I figured splitting it up into more specific tables would make it go faster but it you have other suggestions as far as optimizing things, I'd be more than happy to try it. – Osmium USA Oct 11 '13 at 23:41

0 Answers0