Re: Adding of indexes, best practises

On Wed, May 26, 2010 at 4:47 AM, Machiel Richards <machielr(at)rdc(dot)co(dot)za>wrote:

> Good day all
>
>
>
> Maybe someone could help me out from a resource point of
> view or even from an experience point of view.
>
>
>
> I have been a dba for only a short while now and the
> training I did was not as intensive as you would get in cases like Oracle
> for instance.
>
>
>
> I am currently involved in a project for one of the clients
> where they are busy consolidating and migrating some of their systems.
>
>
>
> One of the tasks I need to do is to look at some of the
> queries that are quite lengthy and running for long periods, and then
> determine whether there are any indexes that should be added, etc...
>
>
>
> I know I can use the explain option to see what the query
> time will be as well as some other info, however from experience point of
> view this does not mean much to me yet.
>
>
>
> Can someone direct me to some info or assist regarding the
> following:
>
>
>
> · What does the explain output mean and how to interpret the
> output to determine how queries can be optimised.
>
> · Is there a guideline in terms of when / where indexes should be
> added or will be beneficial? How do I determine of the query needs and index
> or not.
>
>
>

As a VERY rough rule of thumb, any column that appears in a where clause or
is used in a join should be indexed.

Sean