mdcallag

--------------
set long_query_time=0
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
flush status
--------------

mdcallag

--------------
drop table if exists ten
--------------

Query OK, 0 rows affected (0.03 sec)

--------------
drop table if exists one_k
--------------

mdcallag

drop table if exists ten;
drop table if exists one_k;
drop table if exists tq;

create table ten(a int primary key);
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table one_k(a int primary key);
insert into one_k select a.a + b.a* 10 + c.a * 100 from ten a, ten b, ten c;
#insert into one_k select a.a + b.a* 10 from ten a, ten b;

mdcallag

--------------
set long_query_time=0
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
SELECT max(pk), j FROM tq GROUP BY j
--------------

mdcallag

--------------
drop table if exists ti
--------------

Query OK, 0 rows affected (0.06 sec)

--------------
drop table if exists tq
--------------

mdcallag

set long_query_time=0;

# Query with loose index scan optimization
SELECT max(pk), j FROM tq GROUP BY j;

# Uncorrelated subquery
SELECT t1.pk, t1.j, t1.k
FROM tq t1, (SELECT max(pk) as maxpk, j FROM tq GROUP BY j) t2
WHERE t2.maxpk = t1.pk;

mdcallag

drop table if exists ti; drop table if exists tq;

create table ti(i int);
create table tq(pk int primary key auto_increment, j int NOT NULL, k int NOT NULL);

insert into ti values (null), (null), (null), (null);
insert into ti select null from ti;
insert into ti select null from ti;
insert into ti select null from ti;
insert into ti select null from ti;

mdcallag

explain
SELECT * FROM tags, LATERAL (SELECT * FROM cpu WHERE cpu.tags_id = tags.id ORDER BY time DESC LIMIT 1) as lp ORDER by tags.hostname, lp.time DESC

Query takes 84+ seconds, examines 300 rows?

# Time: 2020-11-05T20:12:25.702338Z
# User@Host: root[root] @ localhost []  Id:    41
# Query_time: 84.049762  Lock_time: 0.000119 Rows_sent: 100  Rows_examined: 300
SET timestamp=1604607061;
SELECT * FROM tags, LATERAL (SELECT * FROM cpu WHERE cpu.tags_id = tags.id ORDER BY time DESC LIMIT 1) as lp ORDER by tags.hostname, lp.time DESC;

mdcallag

mysql> select * from information_schema.optimizer_trace\G
*************************** 1. row ***************************
                            QUERY: SELECT * FROM cpu WHERE usage_user > 90.0 and time >= '2016-01-01 00:26:02' AND time < '2016-01-01 12:26:02' AND tags_id IN (SELECT id FROM tags WHERE hostname IN ('host_2'))
                            TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {

mdcallag

--- query plan with MySQL 8.0.21

mysql> show create table cpu\G
*************************** 1. row ***************************
       Table: cpu
Create Table: CREATE TABLE `cpu` (
  `time` timestamp NOT NULL,
  `tags_id` bigint NOT NULL,
  `additional_tags` text,
  `usage_user` double NOT NULL,