Optimzing MySQL many to many query

Question

Hi,

I've having problems getting MySQL to use indexes on a many to many query, i have pasted the relative information below.

EXPLAIN  SELECT *
FROM interviews
JOIN interview_category_links ON interviews.id = interview_category_links.inter_id
JOIN categories ON interview_category_links.cat_id = categories.id
WHERE categories.category_safe = 'news'
ORDER BY date DESC
LIMIT 15 

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra
1  SIMPLE  categories  ALL  PRIMARY,id  NULL  NULL  NULL  16  Using where; Using temporary; Using filesort
1  SIMPLE  interview_category_links  ref  inter_id,inter_id_2,cat_id  cat_id  4  devel.categories.id  893   
1  SIMPLE  interviews  eq_ref  PRIMARY  PRIMARY  4  devel.interview_category_links.in...  1

On the categories table I have the index (id, cat_id) and on the interview_category_links table i have both (cat_id, inter_id) and individual indexes as well (cat_id) and (inter_id)

If anyone can shed some light on this I would be very greatful.

Thanks Tom

Answer 1

Do you have an index on the interviews(id)?

What if you removed the "order by date desc"? Does the query run faster? If so, you may want to add an index to that field (which, I assume, is a column in the interviews table).