Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have two tables, table A which contains date and name of table B entry and table B which contains its name and type. Table a has a composite index on table_b_name and date. I'm selecting all table A entries which are connected to table B having certain type.

The question is why the composite index is not being used?

Hede is the DDL and selects:

CREATE  TABLE tbl_a (
  id INT(11) NOT NULL AUTO_INCREMENT ,
  tbl_b_name VARCHAR(64) NOT NULL ,
  date TIMESTAMP NULL DEFAULT NULL ,  
  PRIMARY KEY (id) ,
  INDEX idx_1 (tbl_b_name, date ASC)
)
ENGINE = MyIsam
DEFAULT CHARACTER SET = utf8;

CREATE  TABLE tbl_b (
  id INT(11) NOT NULL AUTO_INCREMENT ,
  name VARCHAR(64) NOT NULL ,
  type VARCHAR(64) NOT NULL ,  
  PRIMARY KEY (id)
)
ENGINE = MyIsam
DEFAULT CHARACTER SET = utf8;

INSERT INTO tbl_b (name, type) VALUES ('name1', 'type1');
INSERT INTO tbl_b (name, type) VALUES ('name2', 'type1');
INSERT INTO tbl_b (name, type) VALUES ('name3', 'type2');
INSERT INTO tbl_b (name, type) VALUES ('name4', 'type2');

INSERT INTO tbl_a (tbl_b_name, date) VALUES('name1', '2000-01-01');
INSERT INTO tbl_a (tbl_b_name, date) VALUES('name1', '2010-01-01');
INSERT INTO tbl_a (tbl_b_name, date) VALUES('name2', '2000-01-01');
INSERT INTO tbl_a (tbl_b_name, date) VALUES('name2', '2010-01-01');
INSERT INTO tbl_a (tbl_b_name, date) VALUES('name3', '2000-01-01');
INSERT INTO tbl_a (tbl_b_name, date) VALUES('name3', '2010-01-01');
INSERT INTO tbl_a (tbl_b_name, date) VALUES('name4', '2000-01-01');
INSERT INTO tbl_a (tbl_b_name, date) VALUES('name5', '2010-01-01');

/* index is not used */    
EXPLAIN
SELECT a.* 
FROM tbl_a a 
INNER JOIN tbl_b b 
ON b.name = a.tbl_b_name AND b.type = 'type1';

/* index is not used */
EXPLAIN
SELECT * 
FROM tbl_a 
WHERE tbl_b_name IN (SELECT name FROM tbl_b WHERE type = 'type1');

/* index is used! */    
EXPLAIN
SELECT * 
FROM tbl_a 
WHERE tbl_b_name IN ('name1', 'name2');

SqlFiddle

share|improve this question
1  
You can also try the queries after adding a (type,name) index. –  ypercube Oct 16 '13 at 13:50
    
Wow! That works :) But why? –  Nailgun Oct 16 '13 at 14:03
    
You should need also create an index on tbl_b.type.. and maybe use InnoDB as engine.. the MySQL optimizer does this sqlfiddle.com/#!2/41963/1 on your tables. vs MyISAM sqlfiddle.com/#!2/40fa6f/1 the MySQL does an better job for optimizing non-correlated sub queries on InnoDB tables but still needs an FULL index scan vs an FULL table scan on MyISAM engine, what could be better because indexes are most likly to be smaller in size unless you indexed every column in the table.. –  Raymond Nijland Oct 16 '13 at 19:38
    
Still iám wondering why you don't use an tbl_a.tbl_b.id (INT) as JOIN condition, INTS are faster vs VARCHAR when joining you use an key lenght off 194 bytes (because off utf8) now... if you had joined on an int it was only 4 bits.. –  Raymond Nijland Oct 16 '13 at 19:46
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.