Mysql: create index on 1.4 billion records
- by SiLent SoNG
I have a table with 1.4 billion records. The table structure is as follows:
CREATE TABLE text_page (
text VARCHAR(255),
page_id INT UNSIGNED
) ENGINE=MYISAM DEFAULT CHARSET=ascii
The requirement is to create an index over the column text.
The table size is about 34G.
I have tried to create the index by the following statement:
ALTER TABLE text_page ADD KEY ix_text (text)
After 10 hours' waiting I finally give up this approach.
Is there any workable solution on this problem?
UPDATE: the table is unlikely to be updated or inserted or deleted. The reason why to create index on the column text is because this kind of sql query would be frequently executed:
SELECT page_id FROM text_page WHERE text = ?
