MYSQL multicolumn indexing

Question

I have a query that uses a foreign key and other column(say c1) in where clause. By default, foreign key is indexed. When i add index to c1 and run explain command, the query takes up only foreign key for searching, not the c1 which is also indexed.

I'm not supposed to create a single index for both foreign key and other column. I tried reading comments related to indexing, any suggestions would be really grateful.

Table: Student: Id, Name, year, Class_tutor_id

ClassTutor: Id, name

Select name from Student where Class_tutor_id in (Select id from ClassTutor where name='Sally') and year>=200 and year<=2006;

I have created index in year field. Class_tutor_id is the foreign key referring to ClassTutor table id. When i execute the query with explain command, Under "Keys" column i get the foreign key Class_tutor_id, but in "Possible keys" column, I get foreign key, primary key, year

Edit your question and show the query and the table definitions (or at least the indexes). There isn't enough information to really provide confident guidance. — Gordon Linoff, May 16 '16 at 12:15

score 0 · Answer 1 · answered May 16 '16 at 12:13

0

You should create composite index for the two columns: http://dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html

answered May 16 '16 at 12:13

Omri Ma

1
2

I need to use separate index on each column. I created separate index. In the "EXPLAIN" command, i didn't get "index merge" in type column. The newly created index is not taken into consideration. Is there any other means of doing it? – User12121 May 16 '16 at 12:38
You can't use two separate indexes for the where clause. If you want to use two columns, you have to create new index which will contain both of them. – Omri Ma May 16 '16 at 12:43
when i create new index which contain both of them, the foreign key only get used. It didn't work. The new index is also not getting used – User12121 May 16 '16 at 13:51
Can i use "USE INDEX(index_name)" explicitly in my query? – User12121 May 16 '16 at 14:24
@User12121 - yes you can, it give mySql a hint of which index should be used. you can also FORCE INDEX(index_name) which will force mySql to use it – Omri Ma May 16 '16 at 14:33

score 0 · Answer 2 · answered May 25 '16 at 06:42

Don't use IN ( SELECT ... ), it performs poorly. Use a JOIN instead:

Select  s.name
    from  Student AS s
    JOIN  ClassTutor AS ct  ON ct.id = s.Class_tutor_id
    where  ct.name='Sally'
      and  s.year>=200
      and  s.year<=2006;

Then one of these indexes will be used, and be 'optimal':

Student:  INDEX(year)
ClassTutor:  INDEX(name, id)  -- "composite" and "covering"

Do not USE or FORCE INDEX; it may help today, but hurt tomorrow.

A "separate index on each column" means that you have not learned the power of a "composite" index. See my cookbook.

The FOREIGN KEY is a red hearing in this discussion; focus on the necessary index(es).

MYSQL multicolumn indexing

2 Answers2