0

I have a pretty big table with 300 millions records(table storage is about 13GB).

I have partitioned this table into 10 partitions, but every partition still have almost 30 millions records, that makes the select xxx where column1 = "yyy" very slow (almost 30s every simple select).

Now I want to speed it up by adding a hash index to column1 I use for query data, but it turns out very very very slow to add the index.

So I wonder ,is it possible to do that ? And how do I speed up this operation?

rudolph1024
  • 962
  • 1
  • 12
  • 32
WoooHaaaa
  • 19,732
  • 32
  • 90
  • 138
  • 2
    But don't you just have to add the index once? – Jiminion Jul 29 '13 at 13:22
  • It is possible to index a column, but you have a lot of data and there's no way the operation of adding an index on a 30 mil record table is fast. Unless you have superb hardware, most notably the HDD. – N.B. Jul 29 '13 at 13:23
  • Why HASH instead of BTREE index? – raffian Jul 29 '13 at 13:40
  • @raffian, I thought Hash is much faster than btree. – WoooHaaaa Jul 29 '13 at 13:47
  • @MrROY Not in all cases; HASH is faster for key-based look ups, but does not work for range-based selections, comes down to how you're selecting data (mostly)...see this http://stackoverflow.com/questions/7306316/btree-vs-hashtable – raffian Jul 29 '13 at 13:53
  • You'd benefit more had you used InnoDB and not MyISAM for storage engine.. Contrary to popular belief, InnoDB isn't slower than MyISAM, and more than not - quite faster. – N.B. Jul 29 '13 at 14:31

1 Answers1

0

Finally I finish the create index operation with some changes in my.cnf, here a brief result:

Total time : 2735 seconds.

Records : 300 millions (10 partitions, 30 millions record each).

Create index using hash.

my.cnf:

myisam_sort_buffer_size = 4096M
sort_buffer_size = 4096M

Hope it helps someone else.

Community
  • 1
  • 1
WoooHaaaa
  • 19,732
  • 32
  • 90
  • 138