1
vote
1answer
55 views

When is the index actually created?

I have noticed that even on huge tables with millions of records when I do a CREATE INDEX the index is created immediately (I get the console back in nsecs). Why is this? Doesn't it create the B-Tree ...
1
vote
1answer
101 views

In MySQL if column X has unique values what's the difference between UNIQUE index and B-Tree index

Let's assume that I have table data: CREATE TABLE `test`.`data` ( `row_id` INT NOT NULL , `data_id` INT NOT NULL , PRIMARY KEY (`row_id`) ); Now, let's assume that data_id has unique values. Is ...
5
votes
1answer
190 views

Walking a BTREE index as far as possible in MySQL

Suppose one has a column of words on which one builds a BTREE index: CREATE TABLE myTable ( words VARCHAR(25), INDEX USING BTREE (words) ); LOAD DATA LOCAL INFILE '/usr/share/dict/words' INTO ...
3
votes
4answers
1k views

Benefits of BTREE in MySQL

What are the pros and cons of using a BTREE index in MySQL, regarding query speed, disk storage and memory usage? Does BTREE provide easier iteration in increasing order ? What kind of queries would ...