Re: Create index takes more than 5 hours

[Chris Stubben]

Hi,

You will probably want to purchase 'High Performance MySQL' if you are dealing with large tables...

However, have you tried indexing only the first 5-10 characters in the field?

create index indexname on table(column(5));

You can use mysql as a calculator to compare index sizes for all 55 characters (bytes) vs 5 characters. Some extra storage will be required for row pointers (about 5 bytes per row?).


select count(*)*55/1024/1024/1024 as index_size_GB from bigtable;
or

select 27000000*55/1024/1024/1024 as index_size_GB;
+---------------+
| index_size_GB |
+---------------+
| 1.383014 |
+---------------+
1 row in set (0.00 sec)

mysql> select 27000000*5/1024/1024/1024 as index_size_GB;
+---------------+
| index_size_GB |
+---------------+
| 0.125729 |
+---------------+


Chris