Indexes in MySQL
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).
Subject
Views
Written By
Posted
Indexes in MySQL
2476
September 05, 2005 01:19AM
1591
September 05, 2005 01:12PM
1399
September 05, 2005 10:34PM
1535
September 05, 2005 10:38PM
1376
September 05, 2005 11:40PM
1499
September 07, 2005 06:18AM
1640
September 07, 2005 08:10AM
1382
September 07, 2005 08:41PM
Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.
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.