Re: slow queries on large syslog table

>
>
>
>ive also created a web interface for selecting syslogs based on optional
>combinations of timestamp, hostname and ciscomsg.
>

Combinations is the *magic* word.

>to speed queries i created some indexes on syslog_table :
>    create index syslog_table_stimestamp_index on syslog_table (stimestamp);
>    create index syslog_table_shostid_index on syslog_table (shostid);
>    create index syslog_table_sciscomsgid_index on syslog_table (sciscomsgid);
>    create index syslog_table_shostid_sciscomsgid_index on syslog_table
>(shostid,sciscomsgid);
>
Most of them are of no use. That's what you observed...

I bet you will get much better perfs with:

create index syslog_table_stimestamp_shostid_sciscomsg_index on syslog_table (stimestamt, shostid, sciscomsg);

You can try other combinations, but the one I suggested should be of use
in case you use all three on the query, (or even if you use only the
first, or the first two, though not sure about this last part, in
parentheses).


Believe me. Creating an index on a large table is of no use, unless it
is the right one. As a rule of thumb, include in the index as many of
the SELECTIVE columns present in the WHERE clause as you can.

I am not sure of the selectivity of your columns (never used that
particular structure). There should be some information about that on
some of the system tables. However, I do not know in which, or how to
get that info.

As a second rule of thumb, think how YOU would search for the data you
need if it was written on a paper book, and especially, how you would
like to find the book ordered. For example, if you were to look the
address corresponding to a phone number, you would like to find the data
ordered by phone number, and not by name. Name is not of any use to you.

For selectivity, think of finding the phone numbers of all people that
live at number 5, but of any street, and whose first name is Peter.

Neither "Peter" nor "5" are REALLY useful informations to perform your
search. And even, Peter is more useful than 5.

HTH,

Antonio