Query optimization techniques?

Question

How to optimize queries which are already written?

Answer 1

If you can not change the them: Indexes and statistics.
So you don't optimize the query but their execution plan.

Answer 2

See this previous question.

Answer 3

Use EXPLAIN to see what's going on - what indexes are being used and so on.

Answer 4

If you can't change the query then it really depends on what features are available on your database engine of choice. As Ovidiu said you can use indexes and generate usage statistics to see where the bottleneck is.

Otherwise you can employ techniques like materialised views or horizontal partitioning.

Before you start make sure you know what you're optimisation target is.

Answer 5

IBM Informix Dynamic Server supports a feature that allows you to add optimizer directives to pre-existing SQL when it is executed (without modifying the application). Look up 'external directives' at the Informix web site for more information (or Google 'site:ibm.com informix external directives').