Kako optimiovati ovaj query?

[ 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;

Ima li neko bolju ideju? Da li je uopste moguce optimizovati ovako nesto bez menjanja strukture tabli?