I am not sure anyone has experience like that...

you have around 30-40 tables for an applications, and each table has
around 3-5 indexes on it

some indexes might be useless..due to legacy reason....

are there any simple approach, tools that can analyze which indexes
are not used/or used rarely, so that I can remove them for performance
gain?

anyone want to share?

thanks.

On 23 May 2007 09:34:30 -0700, howa <EMAIL REMOVED> wrote:

>I am not sure anyone has experience like that...
>
>you have around 30-40 tables for an applications, and each table has
>around 3-5 indexes on it
>
>some indexes might be useless..due to legacy reason....
>
>are there any simple approach, tools that can analyze which indexes
>are not used/or used rarely, so that I can remove them for performance
>gain?
>
>anyone want to share?
>
>thanks.

I do not know any tool doing this by itself, maybe it exists.
But with something like :

SELECT tutu.id
FROM tutu
LEFT JOIN tata ON tata.id=tutu.id
WHERE tata.id IS NULL

you can have the ids from tutu that are not in tata.
Creating a procedure using this SELECT repeated for the foreign keys
you want to test would give you a good result, and then up to you to
add a DELETE on these results.