Re: Questions about indexes

On Saturday 10 June 2006 13:30, "Pat Maddox" <pergesu(at)gmail(dot)com> wrote:
> I've got a table with that has 5 fields. Nearly every query I make to
> this table is of the form
> SELECT * FROM table1 WHERE field1='foo' AND field2=7;
>
> It's always those two exact fields. How should I index this to get
> the best performance?

A single index on field1,field2 (or field2, field1) would obviously be
ideal.

>
> Also, how can I find what other parts of my app would benefit from more
> indexes?

Log the queries that are being run, extract the ones that are run the most,
and examine them and their underlying data in detail and with explain
analyze.

If you can think of a way to force programmers to do this while building
their apps, that would also be useful ...

--
In a truly free society, "Alcohol, Tobacco and Firearms" would be a
convenience store chain.