0

I have a table containing 20+ millions(will go to 60+ within month) of records. Now I have to fetch records very fast.

I am using MyISAM engine and i don't want to create problem during insertion(heard indexing can hurt insertion) of records. So please tell me which is better indexing or partitioning.

What other factors(cache or buffering) i should use. Please let me know with example if possible.

Thank you

Aamir
  • 738
  • 2
  • 17
  • 41
  • Read http://stackoverflow.com/questions/3695768/table-with-80-million-records-and-adding-an-index-takes-more-than-18-hours-or-f?rq=1 and http://stackoverflow.com/questions/1358490/is-a-globally-partitioned-index-better-faster-than-a-non-partitioned-index?rq=1 eventually this http://stackoverflow.com/questions/6317593/mysql-index-design-with-table-partitioning?rq=1 – Bud Damyanov Nov 26 '13 at 11:52

1 Answers1

0

Indexing vs. Partitioning is not a 'What's better' question because they aim to solve different problems. Indexing is primarily a performance feature while partitioning is primarily a management feature (to make it easier to administer big databases).

However, there is an overlap as partitioning can also improve performance in some use cases—but not in general, just in some cases. Badly partitioned table can also hurt performance—just like badly designed indexes can.

If you'd like an answer that helps you further you'd need to explain your use case in detail.

Markus Winand
  • 8,371
  • 1
  • 35
  • 44
  • hi Markus, I have already read about it, but lots of insertion is happening in this table. Thats why indexing is not possible so i asked about partitioning or other things like caching. Please let me know what would you do in such case. – Aamir Nov 26 '13 at 15:36
  • @aamir Indexes are the #1 answer to performance issues. Check that first. Why exactly do you think you'r having too many inserts for indexes? – Markus Winand Nov 27 '13 at 05:52
  • actually we already know that this x table is face insert too much if we are going to use index than after some time it will cost us more overhead. But I will check all this cases against it. and thank you for the reply. – Aamir Nov 27 '13 at 06:56
  • @aamir just once more, as a reminder: If you'd like an answer that helps you further you'd need to explain your use case in detail. – Markus Winand Nov 27 '13 at 07:14