Code:
CREATE SCHEMA IF NOT EXISTS sampledb DEFAULT CHARACTER SET='utf8' DEFAULT COLLATE='utf8_general_ci';
CREATE TABLE `sampledb`.`block` (
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `Index_2` (`name`)
) ENGINE=InnoDB;
CREATE TABLE `sampledb`.`block_instance` (
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`blockname` VARCHAR(255) NOT NULL,
`configdata` TEXT,
PRIMARY KEY (`id`),
KEY `Index_3` (`blockname`)
) ENGINE=InnoDB;
CREATE SCHEMA IF NOT EXISTS sampledb DEFAULT CHARACTER SET='utf8' DEFAULT COLLATE='utf8_general_ci';
CREATE TABLE `sampledb`.`block` (
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `Index_2` (`name`)
) ENGINE=InnoDB;
CREATE TABLE `sampledb`.`block_instance` (
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`blockname` VARCHAR(255) NOT NULL,
`configdata` TEXT,
PRIMARY KEY (`id`),
KEY `Index_3` (`blockname`)
) ENGINE=InnoDB;
podaci nisu bitni za ovaj slucaj jer me prevashodno interesuje optimizacija upita odnosno koriscenje ili ne indeksa.
Upit je sledeci:
Code:
SELECT bi.blockname, bi.configdata FROM block_instance bi, block b WHERE bi.blockname = b.name;
SELECT bi.blockname, bi.configdata FROM block_instance bi, block b WHERE bi.blockname = b.name;
Ako stavim explain dobijem sledece:
Code:
+----+-------------+-------+------+---------------+---------+---------+-----------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+-----------------------+------+-------------+
| 1 | SIMPLE | bi | ALL | Index_3 | NULL | NULL | NULL | 1 | |
| 1 | SIMPLE | b | ref | Index_2 | Index_2 | 767 | sampledb.bi.blockname | 1 | Using index |
+----+-------------+-------+------+---------------+---------+---------+-----------------------+------+-------------+
+----+-------------+-------+------+---------------+---------+---------+-----------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+-----------------------+------+-------------+
| 1 | SIMPLE | bi | ALL | Index_3 | NULL | NULL | NULL | 1 | |
| 1 | SIMPLE | b | ref | Index_2 | Index_2 | 767 | sampledb.bi.blockname | 1 | Using index |
+----+-------------+-------+------+---------------+---------+---------+-----------------------+------+-------------+
Ako sklonim polje configdata iz upita i opet izvrsim explain dobije se sledece:
Code:
+----+-------------+-------+-------+---------------+---------+---------+-----------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-----------------------+------+-------------+
| 1 | SIMPLE | bi | index | Index_3 | Index_3 | 767 | NULL | 1 | Using index |
| 1 | SIMPLE | b | ref | Index_2 | Index_2 | 767 | sampledb.bi.blockname | 1 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+-----------------------+------+-------------+
+----+-------------+-------+-------+---------------+---------+---------+-----------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-----------------------+------+-------------+
| 1 | SIMPLE | bi | index | Index_3 | Index_3 | 767 | NULL | 1 | Using index |
| 1 | SIMPLE | b | ref | Index_2 | Index_2 | 767 | sampledb.bi.blockname | 1 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+-----------------------+------+-------------+
Zatim pokusaj 1 da se napravi index sa TEXT poljem
Code:
ALTER TABLE block_instance ADD INDEX blocinst_adv1_idx(id, blockname, configdata(255));
ALTER TABLE block_instance ADD INDEX blocinst_adv1_idx(id, blockname, configdata(255));
pa zatim
Code:
EXPLAIN SELECT bi.blockname, bi.configdata FROM block_instance bi, block b WHERE bi.blockname = b.name;
+----+-------------+-------+------+---------------+---------+---------+-----------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+-----------------------+------+-------------+
| 1 | SIMPLE | bi | ALL | Index_3 | NULL | NULL | NULL | 1 | |
| 1 | SIMPLE | b | ref | Index_2 | Index_2 | 767 | sampledb.bi.blockname | 1 | Using index |
+----+-------------+-------+------+---------------+---------+---------+-----------------------+------+-------------+
EXPLAIN SELECT bi.blockname, bi.configdata FROM block_instance bi, block b WHERE bi.blockname = b.name;
+----+-------------+-------+------+---------------+---------+---------+-----------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+-----------------------+------+-------------+
| 1 | SIMPLE | bi | ALL | Index_3 | NULL | NULL | NULL | 1 | |
| 1 | SIMPLE | b | ref | Index_2 | Index_2 | 767 | sampledb.bi.blockname | 1 | Using index |
+----+-------------+-------+------+---------------+---------+---------+-----------------------+------+-------------+
Kao sto se da videti novi index se ne pojavljuje u listi opcija.
Pokusaj 2 da se napravi index sa TEXT poljem
Code:
ALTER TABLE block_instance ADD INDEX blocinst_adv2_idx(blockname, configdata(255));
ALTER TABLE block_instance ADD INDEX blocinst_adv2_idx(blockname, configdata(255));
pa sledi
Code:
EXPLAIN SELECT bi.blockname, bi.configdata FROM block_instance bi, block b WHERE bi.blockname = b.name;
+----+-------------+-------+------+---------------------------+---------+---------+-----------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------------------+---------+---------+-----------------------+------+-------------+
| 1 | SIMPLE | bi | ALL | Index_3,blocinst_adv2_idx | NULL | NULL | NULL | 1 | |
| 1 | SIMPLE | b | ref | Index_2 | Index_2 | 767 | sampledb.bi.blockname | 1 | Using index |
+----+-------------+-------+------+---------------------------+---------+---------+-----------------------+------+-------------+
EXPLAIN SELECT bi.blockname, bi.configdata FROM block_instance bi, block b WHERE bi.blockname = b.name;
+----+-------------+-------+------+---------------------------+---------+---------+-----------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------------------+---------+---------+-----------------------+------+-------------+
| 1 | SIMPLE | bi | ALL | Index_3,blocinst_adv2_idx | NULL | NULL | NULL | 1 | |
| 1 | SIMPLE | b | ref | Index_2 | Index_2 | 767 | sampledb.bi.blockname | 1 | Using index |
+----+-------------+-------+------+---------------------------+---------+---------+-----------------------+------+-------------+
Indeks se sada pojavljuje u listi opcija ali se i dalje ne koristi...
Pokusaj 3 - ako nece sa TEXT poljem da probamo drugacije:
Code:
SELECT mbi.blockname, mbi.configdata FROM block_instance mbi,(SELECT bi.id FROM block_instance bi, block b WHERE bi.blockname = b.name) obi WHERE mbi.id = obi.id;
SELECT mbi.blockname, mbi.configdata FROM block_instance mbi,(SELECT bi.id FROM block_instance bi, block b WHERE bi.blockname = b.name) obi WHERE mbi.id = obi.id;
Code:
EXPLAIN SELECT mbi.blockname, mbi.configdata FROM block_instance mbi,(SELECT bi.id FROM block_instance bi, block b WHERE bi.blockname = b.name) obi WHERE mbi.id = obi.id;
+----+-------------+-------+-------+---------------------------+---------+---------+-----------------------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------------------+---------+---------+-----------------------+------+-----------------------------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
| 2 | DERIVED | bi | index | Index_3,blocinst_adv2_idx | Index_3 | 767 | NULL | 1 | Using index |
| 2 | DERIVED | b | ref | Index_2 | Index_2 | 767 | sampledb.bi.blockname | 1 | Using index |
+----+-------------+-------+-------+---------------------------+---------+---------+-----------------------+------+-----------------------------------------------------+
EXPLAIN SELECT mbi.blockname, mbi.configdata FROM block_instance mbi,(SELECT bi.id FROM block_instance bi, block b WHERE bi.blockname = b.name) obi WHERE mbi.id = obi.id;
+----+-------------+-------+-------+---------------------------+---------+---------+-----------------------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------------------+---------+---------+-----------------------+------+-----------------------------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
| 2 | DERIVED | bi | index | Index_3,blocinst_adv2_idx | Index_3 | 767 | NULL | 1 | Using index |
| 2 | DERIVED | b | ref | Index_2 | Index_2 | 767 | sampledb.bi.blockname | 1 | Using index |
+----+-------------+-------+-------+---------------------------+---------+---------+-----------------------+------+-----------------------------------------------------+
Ima li neko bolju ideju? Da li je uopste moguce optimizovati ovako nesto bez menjanja strukture tabli?
Tko leti vrijedi