Skip to content

Instantly share code, notes, and snippets.

View gist:f8ea2f2c6214e4e92e816dfac0f01cd5
MariaDB [test]> analyze format=json select * from t2 where b between 300 and 500 and (b+1) >= (b+2)\G
*************************** 1. row ***************************
ANALYZE: {
"query_optimization": {
"r_total_time_ms": 0.341122797
},
"query_block": {
"select_id": 1,
"cost": 0.28158088,
"r_loops": 1,
@spetrunia
spetrunia / gist:ae773ed2300d1cac543932a9e23bc9a1
Created May 21, 2023 16:17
Partitioned Tables Do Not Support ICP
View gist:ae773ed2300d1cac543932a9e23bc9a1
MariaDB [(none)]> use test;
Database changed
MariaDB [test]> create table t1 (a int, b int, c int, key(b)) partition by hash(a) partitions 10;
Query OK, 0 rows affected (0.025 sec)
MariaDB [test]> show create table t1 \G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
View 1.diff
diff --git a/storage/innobase/rem/rem0rec.cc b/storage/innobase/rem/rem0rec.cc
index f489669b408..ea87750e5bc 100644
--- a/storage/innobase/rem/rem0rec.cc
+++ b/storage/innobase/rem/rem0rec.cc
@@ -437,7 +437,7 @@ rec_init_offsets_comp_ordinary(
if (UNIV_UNLIKELY(len & 0x80) && DATA_BIG_COL(col)) {
/* 1exxxxxxx xxxxxxxx */
len <<= 8;
- len |= *lens--;
+ len |= (rec_offs)*lens--;
View mdev-6768-commit-message.mail
> commit a49d2e4bc62507496b85c3499a423b5b4e321354
> Author: Monty <monty@mariadb.org>
> Date: Fri Mar 31 19:35:04 2023 +0300
>
> MDEV-6768 Wrong result with aggregate with join with no result set
>
> What happens is that end_send_group() is called with a const row but
> without any rows matching the WHERE clause.
> This last part is shown by 'join->first_record' not being set.
>
View mdev-6768-fix-comments.diff
diff -urp 10.5-review2/sql/sql_select.cc 10.5-review2-cl/sql/sql_select.cc
--- 10.5-review2/sql/sql_select.cc 2023-04-04 12:32:42.636312847 +0300
+++ 10.5-review2-cl/sql/sql_select.cc 2023-04-04 13:11:28.732023482 +0300
@@ -26998,7 +26998,7 @@ void inline JOIN::clear_sum_funcs()
/*
Prepare for returning 'empty row' when there is no matching row.
- - Mark all tables with mark_as_null_with()
+ - Mark all tables with mark_as_null_row()
- Make a copy of of all simple SELECT items
View gist:0f50e2e4112466415d39115249ab48aa
create table t1 (a date, b date, c date, key(a),key(b), key(c));
insert into t1
select
date_add('2023-01-01', interval seq day),
date_add('2023-01-01', interval seq day),
date_add('2023-01-01', interval seq day)
from
seq_1_to_1000;
View leap second
./sql/mysql_tzinfo_to_sql /usr/share/zoneinfo/right/Europe/London right/Europe/London > london1.sql
./sql/mysql_tzinfo_to_sql --leap /usr/share/zoneinfo/right/Europe/London > london2.sql
use mysql;
source london1.sql
source london2.sql
<restart the server>;
set time_zone='right/Europe/London';
View enable-one-table-hash-semi-join.diff
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index b971c96cda2..3b6ec949ea3 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -3964,6 +3964,12 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join)
Json_writer_object semijoin_strategy(thd);
semijoin_strategy.add("semi_join_strategy","FirstMatch");
Json_writer_array semijoin_plan(thd, "join_order");
+
+ bool one_table_and_buffer_allowed= false;
View gist:bf4c8d7fc64fab4dca9d0182da4fb152
commit 432a4ebe5cd2ebf4d0fad79092e82e5d1a9f53ba
Author: Michael Widenius <monty@mariadb.org>
Date: Wed May 18 22:17:32 2022 +0300
Improve table pruning in optimizer with up to date key_dependent map
Part of:
MDEV-28073 Slow query performance in MariaDB when using many tables
s->key_dependent has a list of tables that are compared with key fields
View odd-explain-hash-no-jbuf
MariaDB [test]> explain SELECT SUM( l_partkey ) FROM region JOIN nation ON ( r_regionkey = n_regionkey ) LEFT JOIN supplier ON ( s_nationkey = n_nationkey ) JOIN partsupp ON ( s_suppkey = ps_suppkey ) RIGHT JOIN lineitem ON ( ps_partkey = l_partkey AND ps_suppkey = l_suppkey ) JOIN orders ON ( l_orderkey = o_orderkey ) WHERE o_comment IS NOT NULL OR n_comment IS NOT NULL AND ps_availqty = l_quantity\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL