Hello I have built this queries thanks answers to my questions: How to get data from three different tables and How to show rows with 0 without any records in database But now I have performance problem with my database.
When I use this select my MySQL server freezes. SQLFiddle example.
SELECT s.ip
, COUNT(DISTINCT a.id) as 'ALLLogin'
, COUNT(DISTINCT CASE WHEN a.success = 1 THEN a.id END) as SuccessLogin
, COUNT(DISTINCT CASE WHEN a.success = 0 THEN a.id END) as UnsucLogin
, COUNT(DISTINCT s.id)
, COUNT(DISTINCT i.id) as TotalInputs
, COUNT(DISTINCT i.input) as UniqInputs
, COUNT(DISTINCT d.id) as files
, COUNT(DISTINCT d.shasum) as Uniqfiles
FROM sessions s
LEFT JOIN auth a ON s.id = a.session
LEFT JOIN input i ON s.id = i.session
LEFT JOIN downloads d ON s.id = d.session
GROUP BY s.ip
ORDER BY COUNT(DISTINCT i.id) DESC
LIMIT 30;
EXPLAIN
+----+-------------+-------+------+---------------+---------+---------+------------+---------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+------------+---------+---------------------------------+
| 1 | SIMPLE | s | ALL | NULL | NULL | NULL | NULL | 2484724 | Using temporary; Using filesort |
| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 4066566 | |
| 1 | SIMPLE | i | ref | session | session | 32 | data1.s.id | 2 | |
| 1 | SIMPLE | d | ref | session | session | 32 | data1.s.id | 1 | |
+----+-------------+-------+------+---------------+---------+---------+------------+---------+---------------------------------+
When I use this select, it takes more than 20 minutes…
SELECT `date`, count(t.`id`)
FROM (
SELECT @date := date_add(@date, INTERVAL 1 DAY) as date
FROM viewDec d10, viewDec d100, viewDec d1000
, (SELECT @date := date_add(@date_start, INTERVAL -1 DAY)) v
WHERE @date < @date_end
) d
LEFT JOIN sessions t
ON d.`date` = date_format(t.starttime, '%Y-%m-%d')
GROUP BY d.`date`
;
EXPLAIN
+----+--------------+--------------------------------------+--------+---------------+-----------+---------+------+---------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+--------------------------------------+--------+---------------+-----------+---------+------+---------+---------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 425 | Using temporary; Using filesort |
| 1 | PRIMARY | t | index | NULL | starttime | 12 | NULL | 2484743 | Using index |
| 2 | DERIVED | <derived3> | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DERIVED | <derived4> | ALL | NULL | NULL | NULL | NULL | 10 | |
| 2 | DERIVED | <derived14> | ALL | NULL | NULL | NULL | NULL | 10 | Using join buffer |
| 2 | DERIVED | <derived24> | ALL | NULL | NULL | NULL | NULL | 10 | Using where; Using join buffer |
| 24 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 25 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 26 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 27 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 28 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 29 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 30 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 31 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 32 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 33 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| NULL | UNION RESULT | <union24,25,26,27,28,29,30,31,32,33> | ALL | NULL | NULL | NULL | NULL | NULL | |
| 14 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 15 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 16 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 17 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 18 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 19 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 20 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 21 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 22 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 23 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| NULL | UNION RESULT | <union14,15,16,17,18,19,20,21,22,23> | ALL | NULL | NULL | NULL | NULL | NULL | |
| 4 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 5 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 6 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 7 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 8 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 9 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 10 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 11 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 12 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 13 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| NULL | UNION RESULT | <union4,5,6,7,8,9,10,11,12,13> | ALL | NULL | NULL | NULL | NULL | NULL | |
| 3 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+--------------+--------------------------------------+--------+---------------+-----------+---------+------+---------+---------------------------------+
Is possible optimize this database or queries please?
Tables:
sessions
CREATE TABLE `sessions` (
`id` char(32) NOT NULL,
`starttime` datetime NOT NULL,
`endtime` datetime DEFAULT NULL,
`sensor` int(4) NOT NULL,
`ip` varchar(15) NOT NULL DEFAULT '',
`termsize` varchar(7) DEFAULT NULL,
`client` int(4) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `starttime` (`starttime`,`sensor`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
input
CREATE TABLE `input` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`session` char(32) NOT NULL,
`timestamp` datetime NOT NULL,
`realm` varchar(50) DEFAULT NULL,
`success` tinyint(1) DEFAULT NULL,
`input` text NOT NULL,
PRIMARY KEY (`id`),
KEY `session` (`session`,`timestamp`,`realm`)
) ENGINE=InnoDB AUTO_INCREMENT=185744 DEFAULT CHARSET=latin1;
downloads
CREATE TABLE `downloads` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`session` char(32) NOT NULL,
`timestamp` datetime NOT NULL,
`url` text NOT NULL,
`outfile` text NOT NULL,
`shasum` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `session` (`session`,`timestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=23051 DEFAULT CHARSET=latin1;
auth
CREATE TABLE `auth` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`session` char(32) NOT NULL,
`success` tinyint(1) NOT NULL,
`username` varchar(100) NOT NULL,
`password` varchar(100) NOT NULL,
`timestamp` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4063227 DEFAULT CHARSET=latin1;
AFTER CHANGES
SELECT `date`, count(t.`id`)
FROM (
SELECT @date := date_add(@date, INTERVAL 1 DAY) as date
FROM viewDec d10, viewDec d100, viewDec d1000
, (SELECT @date := date_add(@date_start, INTERVAL -1 DAY)) v
WHERE @date < @date_end
) d
LEFT JOIN sessions t
ON t.starttime BETWEEN d.`date` AND date_add(d.`date`, INTERVAL 1 DAY)
GROUP BY d.`date`
;
EXPLAIN
+----+--------------+--------------------------------------+--------+---------------+-----------+---------+------+---------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+--------------------------------------+--------+---------------+-----------+---------+------+---------+---------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 425 | Using temporary; Using filesort |
| 1 | PRIMARY | t | index | starttime | starttime | 12 | NULL | 2805818 | Using index |
| 2 | DERIVED | <derived3> | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DERIVED | <derived4> | ALL | NULL | NULL | NULL | NULL | 10 | |
| 2 | DERIVED | <derived14> | ALL | NULL | NULL | NULL | NULL | 10 | Using join buffer |
| 2 | DERIVED | <derived24> | ALL | NULL | NULL | NULL | NULL | 10 | Using where; Using join buffer |
| 24 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 25 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 26 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 27 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 28 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 29 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 30 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 31 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 32 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 33 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| NULL | UNION RESULT | <union24,25,26,27,28,29,30,31,32,33> | ALL | NULL | NULL | NULL | NULL | NULL | |
| 14 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 15 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 16 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 17 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 18 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 19 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 20 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 21 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 22 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 23 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| NULL | UNION RESULT | <union14,15,16,17,18,19,20,21,22,23> | ALL | NULL | NULL | NULL | NULL | NULL | |
| 4 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 5 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 6 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 7 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 8 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 9 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 10 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 11 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 12 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 13 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| NULL | UNION RESULT | <union4,5,6,7,8,9,10,11,12,13> | ALL | NULL | NULL | NULL | NULL | NULL | |
| 3 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+--------------+--------------------------------------+--------+---------------+-----------+---------+------+---------+---------------------------------+
Best Answer
Query #1:
You are missing indexes on
sessions.ipandauth.session. The latter is really important because now during the join for each row insessions(~2M) MySQL has to check all ~4M rows in theauthtable instead of picking the one or few directly using the index. That means the query is maybe 1,000,000 times slower than it needs to be. You can make the index on(session, success)to cover all uses of that table in this query. The index onipallows for an index scan instead of full table scan (and possibly a group by optimization), but cannot help with the filesort, so its effect won't probably be so big.Query #2:
means no index can be used to find the right rows, because you call a function over the
starttimecolumn. Instead write is asThat will allow the use of index and the number of rows checked during the row will be much smaller.
Edit for 5.5:
http://sqlfiddle.com/#!2/09be9a/4