Skip to content

Instantly share code, notes, and snippets.

Avatar
🎯
Focusing

Mark Callaghan mdcallag

🎯
Focusing
View GitHub Profile
View gist:3c40a6cc8a673469e94b28d2c55027d2
--------------
set long_query_time=0
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
flush status
--------------
View gist:ce7575d9ea578390d5c1a87679039b77
--------------
drop table if exists ten
--------------
Query OK, 0 rows affected (0.03 sec)
--------------
drop table if exists one_k
--------------
View gist:2140797c81d4e97088a8555ea5041dab
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;
View gist:8439a0dbfe8e724fe3acc4f89dec682a
--------------
set long_query_time=0
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
SELECT max(pk), j FROM tq GROUP BY j
--------------
View gist:4ebb8c7471dc0c222e8ee402431d6fa8
--------------
drop table if exists ti
--------------
Query OK, 0 rows affected (0.06 sec)
--------------
drop table if exists tq
--------------
View gist:b81392f4ba7f969c12e6022b3eac117b
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;
View gist:14596686d5dbcd10eee6616fa662be80
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;
View gist:cf62a30505dc80de5911371e7b2a8954
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;
View gist:0909abdcc5d3f021a2576960584b384d
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": [
{
View gist:99ba5feccb62002b2b63ee23bbcb55fa
--- 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,
You can’t perform that action at this time.