Shulou(Shulou.com)06/01 Report--

Mysql queries a partition table, executes OK when the query condition has data, and executes endlessly when there is no data, always in sending data, and there is no problem when desc is removed. There seems to be no problem with a different version.

Mysql > select version ()

+-+

| | version () |

+-+

| | 5.7.11-log |

+-+

1 row in set (0.00 sec)

Mysql > use zabbix

Database changed

Mysql > SELECT * FROM history h WHERE h.itemidroads 106107' AND h.clock > 1533723653 ORDER BY h.clock DESC LIMIT 2 OFFSET 0

+-+

| | itemid | clock | value | ns | |

+-+

| | 106107 | 1533828123 | 1792.0000 | 151803000 | |

| | 106107 | 1533828003 | 1792.0000 | 44536142 | |

+-+

2 rows in set (0.00 sec)

Mysql > explain SELECT * FROM history h WHERE h.itemidroads 106107' AND h.clock > 1533723653 ORDER BY h.clock DESC LIMIT 2 OFFSET 0

+- -+ -+

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+- -+ -+

| | 1 | SIMPLE | h | p201809 department p201810 people p201811 people p201812 people p201901 people p201902memorie p201903famiy p201904department p201906department p201907menorp201908magenta p201909menmenp201911p201912 | range | history_1,idx_history_clock | history_1 | 12 | NULL | 172 | 100.00 | Using index condition | |

+- -+ -+

1 row in set, 1 warning (0.05sec)

Ysql > SELECT * FROM history h WHERE h.itemidroads 1061055' AND h.clock > 1533723653 ORDER BY h.clock DESC LIMIT 2 OFFSET 0

^ C ^ C-- query aborted

ERROR 1317 (70100): Query execution was interrupted

Mysql > explain SELECT * FROM history h WHERE h.itemidroads 1061055' AND h.clock > 1533723653 ORDER BY h.clock DESC LIMIT 2 OFFSET 0

+- -+ -+

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+- -+ -+

| | 1 | SIMPLE | h | p201809 department p201810 people p201811 people p201812 people p201901 people p201902 Magical p201903Magical p201904Currentp201906Cinder p201907Magnee p201908Magne201909formp201911memorialp201912 | range | history_1,idx_history_clock | history_1 | 12 | NULL | 1 | 100.00 | Using index condition | Using index condition |

+- -+ -+

1 row in set, 1 warning (0.00 sec)

Mysql > SELECT * FROM history h WHERE h.itemidroads 1061055' AND h.clock > 1533723653 ORDER BY h.clock LIMIT 2 OFFSET 0

Empty set (0.00 sec)

Mysql > explain SELECT * FROM history h WHERE h.itemidroads 1061055' AND h.clock > 1533723653 ORDER BY h.clock LIMIT 2 OFFSET 0

+- -+ -+

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+- -+ -+

| | 1 | SIMPLE | h | p201809 department p201810 people p201811 people p201812 people p201901 people p201902 Magical p201903Magical p201904Currentp201906Cinder p201907Magnee p201908Magne201909formp201911memorialp201912 | range | history_1,idx_history_clock | history_1 | 12 | NULL | 1 | 100.00 | Using index condition | Using index condition |

+- -+ -+

1 row in set, 1 warning (0.00 sec)

The descending index is created to improve the efficiency.

Mysql > create index idx_history_2 on history (itemid desc)

Query OK, 0 rows affected (36 min 50.11 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql >

Mysql > SELECT * FROM history h WHERE h.itemidroads 1060001055' AND h.clock > 1533723653 ORDER BY h.clock DESC LIMIT 2 OFFSET 0

Empty set (0.00 sec)

Mysql > explain SELECT * FROM history h WHERE h.itemidroads 1060001055' AND h.clock > 1533723653 ORDER BY h.clock DESC LIMIT 2 OFFSET 0

+- -+- -+

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+- -+- -+

| | 1 | SIMPLE | h | p201809 department p201810 people p201811 people p201812 people p201901 people p201902 Magical p201903Magical p201904Curryp201906famine p201907Magnee p201908Magneto 2019099famalp201911mementp201912 | ref | history_1,idx_history_clock,idx_history_2 | history_1 | 8 | const | 1 | 31.59 | Using where | Using where |

+- -+- -+

1 row in set, 1 warning (0.00 sec)

Upgraded version, improved efficiency

Mysql >

Mysql > select version ()

+-+

| | version () |

+-+

| | 5.7.22-22-log |

+-+

1 row in set (0.00 sec)

Mysql > SELECT * FROM history h WHERE h.itemidroads 1060001055' AND h.clock > 1533723653 ORDER BY h.clock DESC LIMIT 2 OFFSET 0

ERROR 1046 (3D000): No database selected

Mysql > show databses

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'databses' at line 1

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | sys |

| | zabbix |

+-+

5 rows in set (0.00 sec)

Mysql > use zabbix

Database changed

Mysql > SELECT * FROM history h WHERE h.itemidroads 1060001055' AND h.clock > 1533723653 ORDER BY h.clock DESC LIMIT 2 OFFSET 0

Empty set (0.01sec)

Mysql > explain SELECT * FROM history h WHERE h.itemidroads 1060001055' AND h.clock > 1533723653 ORDER BY h.clock DESC LIMIT 2 OFFSET 0

+- -+ -+

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+- -+ -+

| | 1 | SIMPLE | h | p201809 department p201810 people p201811 people p201812 people p201901 people p201902 Magical p201903Magical p201904Currentp201906Cinder p201907Magnee p201908Magne201909formp201911memorialp201912 | range | history_1,idx_history_clock | history_1 | 12 | NULL | 1 | 100.00 | Using index condition | Using index condition |

+- -+ -+

1 row in set, 1 warning (0.00 sec)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.