You have a partial (i.e., three-part) index 'time', 'node_id', 'status' from which you appear to be selecting against only two parts. On the mysql command line run the query by hand preceded by the keywprd "explain"
explain select nodes blah blah blah You should get back a listing that will let you see how the indexes are being used (or abused!). You may discover that in addition to the composite index you have that two additional indexes will help. ~Charles~ On Tue, Apr 12, 2011 at 9:15 AM, Vincent <vlouvi...@gmail.com> wrote: > I have noticed thaht when I remove ((reports.kind = ‘apply’ AND > reports.status = ‘failed’) part from the query, the query is very > quick. > > mysql> show index from reports from dashboard; > +---------+------------+---------------------------------------------- > +--------------+-------------+-----------+-------------+---------- > +--------+------+------------+---------+ > | Table | Non_unique | Key_name > | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | > Packed | Null | Index_type | Comment | > +---------+------------+---------------------------------------------- > +--------------+-------------+-----------+-------------+---------- > +--------+------+------------+---------+ > | reports | 0 | PRIMARY > | 1 | id | A | 112252 | NULL | > NULL | | BTREE | | > | reports | 1 | index_reports_on_node_id > | 1 | node_id | A | 652 | NULL | > NULL | YES | BTREE | | > | reports | 1 | index_reports_on_time_and_node_id_and_status > | 1 | time | A | 112252 | NULL | > NULL | YES | BTREE | | > | reports | 1 | index_reports_on_time_and_node_id_and_status > | 2 | node_id | A | 112252 | NULL | > NULL | YES | BTREE | | > | reports | 1 | index_reports_on_time_and_node_id_and_status > | 3 | status | A | 112252 | NULL | > NULL | YES | BTREE | | > +---------+------------+---------------------------------------------- > +--------------+-------------+-----------+-------------+---------- > +--------+------+------------+---------+ > 5 rows in set (0.01 sec) > > > > On Apr 12, 4:04 pm, Charles Johnson <gm.johns...@gmail.com> wrote: > > What indexes do you have on the respective tables? > > > > > > > > > > > > > > > > On Tue, Apr 12, 2011 at 5:25 AM, Vincent <vlouvi...@gmail.com> wrote: > > > I have increase the mysql like this but the queries takes still more > > > than 9sec for the first page. > > > any other suggestions for the mysql tuning ? > > > > > key_buffer=64M > > > sort_buffer=4M > > > query_cache_size = 20M > > > read_buffer_size=1M > > > > > Node Load (9090.5ms) SELECT `nodes`.* FROM `nodes` INNER JOIN > > > `reports` ON reports.node_id = nodes.id WHERE ((reports.kind = 'apply' > > > AND reports.status != 'failed') AND (`nodes`.`hidden` = 0)) GROUP BY > > > nodes.id > > > Node Load (9082.9ms) SELECT `nodes`.* FROM `nodes` INNER JOIN > > > `reports` ON reports.node_id = nodes.id WHERE ((reports.kind = 'apply' > > > AND reports.status = 'failed') AND (`nodes`.`hidden` = 0)) GROUP BY > > > nodes.id > > > > > On Apr 11, 4:19 pm, Vincent <vlouvi...@gmail.com> wrote: > > > > Thanks > > > > > > I have make the > > > > rake RAILS_ENV=production db:raw:optimize > > > > and in the my.cnf > > > > query_cache_size = 20000000 > > > > > > Its really better > > > > > > Vincent > > > > > > On 11 avr, 14:26, Mohamed Lrhazi <lrh...@gmail.com> wrote: > > > > > > > There's some rake tasks, and other things, suggested here: > > >https://github.com/puppetlabs/puppet-dashboard > > > > > > > On Mon, Apr 11, 2011 at 5:29 AM, Vincent <vlouvi...@gmail.com> > wrote: > > > > > > Hello, > > > > > > > > since the last upgrade to V1.1.0 > > > > > > The dashboard is very slow > > > > > > > > I notice this slow queries in the log : > > > > > > > > Node Load (14178.9ms) SELECT `nodes`.* FROM `nodes` INNER JOIN > > > > > > `reports` ON reports.node_id = nodes.id WHERE ((reports.kind = > > > 'apply' > > > > > > AND reports.status != 'failed') AND (`nodes`.`hidden` = 0)) GROUP > BY > > > > > > nodes.id > > > > > > Node Load (13149.9ms) SELECT `nodes`.* FROM `nodes` INNER JOIN > > > > > > `reports` ON reports.node_id = nodes.id WHERE ((reports.kind = > > > 'apply' > > > > > > AND reports.status = 'failed') AND (`nodes`.`hidden` = 0)) GROUP > BY > > > > > > nodes.id > > > > > > > > How can i optimize the DB ? > > > > > > > > Vincent > > > > > > > > -- > > > > > > You received this message because you are subscribed to the > Google > > > Groups "Puppet Users" group. > > > > > > To post to this group, send email to > puppet-users@googlegroups.com. > > > > > > To unsubscribe from this group, send email to > > > puppet-users+unsubscr...@googlegroups.com. > > > > > > For more options, visit this group athttp:// > > > groups.google.com/group/puppet-users?hl=en. > > > > > -- > > > You received this message because you are subscribed to the Google > Groups > > > "Puppet Users" group. > > > To post to this group, send email to puppet-users@googlegroups.com. > > > To unsubscribe from this group, send email to > > > puppet-users+unsubscr...@googlegroups.com. > > > For more options, visit this group at > > >http://groups.google.com/group/puppet-users?hl=en. > > -- > You received this message because you are subscribed to the Google Groups > "Puppet Users" group. > To post to this group, send email to puppet-users@googlegroups.com. > To unsubscribe from this group, send email to > puppet-users+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/puppet-users?hl=en. > > -- You received this message because you are subscribed to the Google Groups "Puppet Users" group. To post to this group, send email to puppet-users@googlegroups.com. To unsubscribe from this group, send email to puppet-users+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/puppet-users?hl=en.
