[ kiklop74 @ 25.01.2012. 16:08 ] @
Da bih ispravno ilustrovao problem evo minimalno SQL skripta koji kreira bazu i dve tabele. 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; 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; 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 | +----+-------------+-------+------+---------------+---------+---------+-----------------------+------+-------------+ 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 | +----+-------------+-------+-------+---------------+---------+---------+-----------------------+------+-------------+ Zatim pokusaj 1 da se napravi index sa TEXT poljem Code: 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 | +----+-------------+-------+------+---------------+---------+---------+-----------------------+------+-------------+ 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)); 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 | +----+-------------+-------+------+---------------------------+---------+---------+-----------------------+------+-------------+ 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; 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 | +----+-------------+-------+-------+---------------------------+---------+---------+-----------------------+------+-----------------------------------------------------+ Ima li neko bolju ideju? Da li je uopste moguce optimizovati ovako nesto bez menjanja strukture tabli? |