MySQL Forums
Forum List  »  InnoDB

slow query in Inndb
Posted by: Sylwester Zdanowski
Date: May 29, 2012 09:32AM

I hope this section will be ok for this.
I have database using InnoDB engine. Unfortunately one query needs over 7 seconds to compleat. I can't find what makes is so slow. Time is not connected with hdd however CPU is working at 100% to make this query. I saw the same database with more data working in one-two seconds. Probably because of change in structure which I can't find. I checked also for triggers tried optymize and such.
SELECT c.id AS id, lastname AS customername, status, address, zip,
city, countryid, countries.name AS country, email, ten, ssn, c.info AS
info, message, c.divisionid, c.paytime AS paytime, COALESCE(b.value, 0) AS
balance, COALESCE(t.value, 0) AS tariffvalue, s.account, s.warncount,
s.online, (CASE WHEN s.account = s.acsum THEN 1 WHEN s.acsum > 0 THEN 2
ELSE 0 END) AS nodeac, (CASE WHEN s.warncount = s.warnsum THEN 1 WHEN
s.warnsum > 0 THEN 2 ELSE 0 END) AS nodewarn FROM customersview c LEFT
JOIN countries ON (c.countryid = countries.id) LEFT JOIN (SELECT
SUM(value) AS value, customerid FROM cash GROUP BY customerid) b ON
(b.customerid = c.id) LEFT JOIN (SELECT customerid, SUM((1)) AS value FROM
assignments LEFT JOIN tariffs t ON (t.id = tariffid) LEFT JOIN liabilities
l ON (l.id = liabilityid AND period != 0) WHERE (datefrom <= 1337932817 OR
datefrom = 0) AND (dateto > 1337932817 OR dateto = 0) GROUP BY customerid)
t ON (t.customerid = c.id) LEFT JOIN (SELECT ownerid, SUM(access) AS
acsum, COUNT(access) AS account, SUM(warning) AS warnsum, COUNT(warning)
AS warncount, (CASE WHEN MAX(lastonline) > 1337932817 - 0 THEN 1 ELSE 0
END) AS online FROM nodes WHERE ownerid > 0 GROUP BY ownerid ) s ON
(s.ownerid = c.id);
EXPLAIN shows:
Lp.	id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	1	PRIMARY	c	ALL					939	Using where
2	1	PRIMARY	countries	eq_ref	PRIMARY	PRIMARY	4	lms.c.countryid	1	
3	1	PRIMARY	<derived2>	ALL					1281	
4	1	PRIMARY	<derived3>	ALL					642	
5	1	PRIMARY	<derived4>	ALL					644	
6	6	DEPENDENT SUBQUERY	e	index		userid	8		276	Using where; Using index
7	6	DEPENDENT SUBQUERY	a	eq_ref	customerassignment	customerassignment	8	lms.e.customergroupid,lms.c.id	1	Using where; Using index
8	4	DERIVED	nodes	index	ownerid	ownerid	4		1688	Using where
9	3	DERIVED	assignments	ALL					772	Using where; Using temporary; Using filesort
10	3	DERIVED	t	eq_ref	PRIMARY	PRIMARY	4	lms.assignments.tariffid	1	Using index
11	3	DERIVED	l	eq_ref	PRIMARY	PRIMARY	4	lms.assignments.liabilityid	1	Using index
12	2	DERIVED	cash	index		customerid	4		53671

Options: ReplyQuote


Subject
Views
Written By
Posted
slow query in Inndb
2723
May 29, 2012 09:32AM
1097
May 30, 2012 08:20PM
1111
May 31, 2012 01:23AM
876
May 31, 2012 09:40PM
965
June 01, 2012 10:31AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.