MySQL Forums
Forum List  »  Performance

Indexes in MySQL
Posted by: Oyvind Fredstie
Date: September 05, 2005 01:19AM

I've noticed something strange in MySQL,
it seems that when I create indexes all the data
from the table is copied over to the index.

I discovered this when I created a btree index on
a table (called xray) that had a blob attribute (10 000 rows
where each blob were approx 250KB) and I noticed that when
I created the index the free space on the harddisk was reduced
by approx. 2.5 GB.

This is the xray table:
CREATE TABLE xray (
xray_num INT(12) NOT NULL AUTO_INCREMENT,
date_captured TIMESTAMP(14) NOT NULL,
xray_image MEDIUMBLOB NOT NULL,
comments TEXT,
consult_num INT(12) NOT NULL,
--
CONSTRAINT XRAY_PK PRIMARY KEY (xray_num),
--
CONSTRAINT XRAY_FK_TO_CONSULTATION FOREIGN KEY (consult_num)
REFERENCES consultation (consult_num)
);

and this is the index:
CREATE INDEX xray_consultnum_btree_index USING BTREE
ON xray (consult_num);

I thought that the index only stored the index key (consult_num)
and a pointer to the row in the table (xray).

Options: ReplyQuote


Subject
Views
Written By
Posted
Indexes in MySQL
2320
September 05, 2005 01:19AM
1495
September 05, 2005 01:12PM
1327
September 05, 2005 10:34PM
1468
September 05, 2005 10:38PM
1302
September 05, 2005 11:40PM
1439
September 07, 2005 06:18AM
1574
September 07, 2005 08:10AM
1306
September 07, 2005 08:41PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.