How to optimize database for specific queries?

Question

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                  |
+----+--------------+--------------------------------------+--------+---------------+-----------+---------+------+---------+---------------------------------+

Use EXPLAIN on those queries and and the results to your question. — jkavalik, Commented Feb 10, 2016 at 7:50
Two things I can see now - you are missing an index on ip in sessions and a second one on session in auth. If the auth table really contains 4M rows as hinted by the autoincrement value, then that one will be really important. — jkavalik, Commented Feb 10, 2016 at 7:56

jkavalik · Accepted Answer · 2016-02-10 15:53:54Z

Query #1:

You are missing indexes on sessions.ip and auth.session. The latter is really important because now during the join for each row in sessions (~2M) MySQL has to check all ~4M rows in the auth table 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 on ip allows 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:

ON d.`date` = date_format(t.starttime, '%Y-%m-%d')

means no index can be used to find the right rows, because you call a function over the starttime column. Instead write is as

ON t.starttime BETWEEN d.`date` AND date_add(d.`date`, INTERVAL 1 DAY)

That will allow the use of index and the number of rows checked during the row will be much smaller.

Edit for 5.5:

SELECT `date`, coalesce(t.c, 0)
 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 (
       select date(starttime) day, count(1) c
       from sessions t
       where starttime BETWEEN date_add(@date_start, INTERVAL -1 DAY) AND @date_end
       group by date(starttime)
   ) t ON t.day = d.`date`
 ;

http://sqlfiddle.com/#!2/09be9a/4

Comments are not for extended discussion; this conversation has been moved to chat. — Paul White, Commented Aug 29, 2017 at 9:46

Stack Exchange Network

How to optimize database for specific queries?

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged
mysql
performance
optimization
query-performance
or ask your own question.

Linked

Hot Network Questions

How to optimize database for specific queries?

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged mysqlperformanceoptimizationquery-performance or ask your own question.

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
mysql
performance
optimization
query-performance
or ask your own question.