I also tried to look at this; since it was causing me issues as well. I had
800,000 rows or so in the reports table but I accidentally corrupted it
(kill -9) so I won't have that many for a few more days.
I created the separate indexex for kind and status but MySQL didn't use
them. It uses in
What seems the best set of indexes to you?
Next, you need to read your mysql docs to "optimize table" and to reindex a
table, especially if there have been lots of writes and/or lots of deletes.
~Charles~
--
You received this message because you are subscribed to the Google Groups
"Puppet User
the rows are respectively 19450 and 1
So I have down from 95691 to 19450
with one index on kind and one on status , it s worst
it use only index_reports_on_kind and 1 X 58631 rows ..
mysql> explain SELECT `nodes`.* FROM `nodes` INNER JOIN `reports` ON
reports.node_id = nodes.id WHERE ((reports
Good try! But I was not clear. Try an index just for kind, and another
separate index just for status. You are down to 4 x 768 (3072) from 95691.
That is much better. Again, drop the create separate indexes for kind and
status and index_reports_on_kind_and_status .
On Tue, Apr 12, 2011 at 2:11 PM
I have add an index
ALTER TABLE `reports` ADD INDEX `index_reports_on_kind_and_status` ( `kind`
, `status` )
it s better but maybe not perfect as the query is performed on each pages in
the dashboard
Thanks
Vincent
mysql> explain SELECT `nodes`.* FROM `nodes` INNER JOIN `reports` ON
reports.no
A couple of things to notice. First, only the nodes.id index and
index_reports_on_node_id
index are used, and since index_reports_on_node_id is used as a ref type you
will have 573 x 167 rows to examine (95,651 more or less) to produce the
rows your sql finally fetches. You might --important: might
here is the result , I don't know what to do :(
mysql> explain 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;
++-+-+---+--
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 y
What indexes do you have on the respective tables?
On Tue, Apr 12, 2011 at 5:25 AM, Vincent 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_cac
