0

I have a query similar to following with more conditions and joins in the sub query.

   EXPLAIN SELECT * FROM (SELECT * FROM test WHERE id = 1) as drv

Subquery alone takes 1sec, because of derived table above query takes 2.5 sec. How can i define index on the derived table. Apart from creating temporary table is there any way to define index. My query is complex enough that it has more joins and conditions on top of the above query that is why i don't want to go for temporary tables..And i feel there must be some way to define indexes apart from temporary tables

Sarath
  • 83
  • 1
  • 8

1 Answers1

1

A derived table is just a macro, not an entity in it's own right.

The query optimizer will the underlying indexes of the base tables. So, you need to ensure good indexes on the base tables for the fully expanded query.

This is why nesting views does not improve performance. A derived table can also be considered an inline view.

The other option is to use temp tables but you have discounted this.

gbn
  • 422,506
  • 82
  • 585
  • 676
  • if there are joins in the subquery...how do optimizer choose index on multiple tables or how to define index in this case – Sarath May 27 '13 at 09:26
  • dynamically creating/dropping temporary tables is taking considerable time. that is why i discounted temp tables.... – Sarath May 27 '13 at 17:06