Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Incomplete
-
10.11.7
Description
Here is my table :
MariaDB [pmacontrol]> show create table mysql_server\G
|
*************************** 1. row *************************** |
Table: mysql_server
|
Create Table: CREATE TABLE `mysql_server` (
|
`id` int(11) NOT NULL AUTO_INCREMENT, |
`id_client` int(11) NOT NULL, |
`id_environment` int(11) NOT NULL, |
`name` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL, |
`display_name` varchar(100) NOT NULL, |
`ip` char(15) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, |
`hostname` varchar(200) NOT NULL DEFAULT '', |
`login` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL, |
`passwd` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, |
`database` varchar(64) NOT NULL, |
`is_password_crypted` int(11) NOT NULL, |
`port` int(11) NOT NULL, |
`ssh_port` int(11) NOT NULL DEFAULT 22, |
`ssh_login` text CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '\'\'', |
`is_sudo` int(11) NOT NULL DEFAULT 0, |
`is_root` int(11) NOT NULL DEFAULT 1, |
`is_monitored` int(11) NOT NULL DEFAULT 1, |
`is_proxy` int(11) NOT NULL DEFAULT 0, |
`is_available` int(11) NOT NULL DEFAULT 0 COMMENT '-1= not asnwered, 0 = HS, 1=OK', |
`is_acknowledged` int(11) NOT NULL DEFAULT 0, |
`error` text NOT NULL DEFAULT '', |
`warning` text NOT NULL DEFAULT '', |
`date_refresh` datetime NOT NULL DEFAULT current_timestamp(),
|
`ssh_available` int(11) NOT NULL DEFAULT 0 COMMENT '-1= not asnwered, 0 = HS, 1=OK', |
`ssh_date_refresh` datetime NOT NULL DEFAULT current_timestamp(),
|
`ssh_error` text NOT NULL DEFAULT '', |
PRIMARY KEY (`id`),
|
UNIQUE KEY `name` (`name`),
|
UNIQUE KEY `ip` (`ip`,`port`),
|
KEY `id_client` (`id_client`),
|
KEY `id_environment` (`id_environment`),
|
KEY `is_monitored` (`is_monitored`,`id_client`),
|
CONSTRAINT `mysql_server_ibfk_1` FOREIGN KEY (`id_client`) REFERENCES `client` (`id`),
|
CONSTRAINT `mysql_server_ibfk_2` FOREIGN KEY (`id_environment`) REFERENCES `environment` (`id`)
|
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci WITH SYSTEM VERSIONING |
1 row in set (0,000 sec) |
the number of line in my table :
MariaDB [pmacontrol]> select table_name, table_rows from information_schema.tables where table_name = 'mysql_server'; |
+--------------+------------+
|
| table_name | table_rows |
|
+--------------+------------+
|
| mysql_server | 16377356 | |
+--------------+------------+
|
1 row in set (0,001 sec) |
Ok about 16 377 356 (with system versioning), but without this my table is about *13 lines *
when i try to the lines :
MariaDB [pmacontrol]> select count(1) from mysql_server; |
+----------+
|
| count(1) | |
+----------+
|
| 13 | |
+----------+
|
1 row in set (25,079 sec) |
OMG ! 25 secondes, if ask for lines it's about (4 min 5,566 sec)
now if ask distinct on table :
MariaDB [pmacontrol]> select distinct id from mysql_server;
|
+----+
|
| id |
|
+----+
|
| 1 | |
| 2 | |
| 3 | |
| 4 | |
| 5 | |
| 6 | |
| 7 | |
| 8 | |
| 9 | |
| 10 | |
| 11 | |
| 12 | |
| 13 | |
+----+
|
13 rows in set (0,000 sec) |
cool it's pretty fast !
work around i found :
MariaDB [pmacontrol]> select count(1) from (select distinct id from mysql_server) as gg; |
+----------+
|
| count(1) | |
+----------+
|
| 13 | |
+----------+
|
1 row in set (0,001 sec) |
For select * from mysql_server => 4 min 5,566 sec
work around :
with t as (select distinct id from mysql_server) select * from mysql_server a inner join t on a.id=t.id;
|
|
|
13 rows in set (0,001 sec) |
If I can find a workaround in mysql, I am sure you can increase the performance internally !