naparuba/shinken — pull request #321 by vaxvms (2012-05-07 09:18:12+00:00)
Title: Change index on logs table for logstore_mysql

My users use the "Service Alert History" of thruk quite a lot and for quite large period of time. They often get a timeout in thruk.

I've found that an index on host_name instead of time is much more useful. I think the index on time is useless because of the manual partitioning made on the livestatus database (1 database per day).

- use_aggressive_sql is set to 1

Here is the livestatus query made by thruk :
```
GET log
Columns: class time type state host_name service_description plugin_output message options contact_name command_name state_type current_host_groups current_service_groups
Filter: time >= 1336172400
Filter: time <= 1336258800
Filter: type = SERVICE ALERT
And: 1
Filter: type = HOST ALERT
And: 1
Filter: type = SERVICE FLAPPING ALERT
Filter: type = HOST FLAPPING ALERT
Filter: type = SERVICE DOWNTIME ALERT
Filter: type = HOST DOWNTIME ALERT
Filter: type ~ starting\.\.\.
Filter: type ~ shutting\ down\.\.\.
Or: 8
Filter: host_name = myhost
Filter: service_description = myservice
And: 5
OutputFormat: json
ResponseHeader: fixed16
```

Without an index on host_name :
```
real 0m1.756s
user 0m0.002s
sys 0m0.001s
```

With an index on host_name :
```
real 0m0.146s
user 0m0.001s
sys 0m0.002s
```

On another side, my first tought was "sqlite sucks, let's use mysql". It's turn out to be false, i get equals running time for mysql and sqlite on sql queries.