MySQL Forums - Optimizer & Parser

MySQL: New JSON format for EXPLAIN (no replies)

Edwin Desouza — Mon, 04 Nov 2024 16:22:09 +0000

MySQL: New JSON format for EXPLAIN
- https://blogs.oracle.com/mysql/post/new-json-format-for-explain

MySQL: EXPLAIN INTO and EXPLAIN FOR SCHEMA (no replies)

Edwin Desouza — Fri, 24 Nov 2023 18:34:31 +0000

MySQL: EXPLAIN INTO and EXPLAIN FOR SCHEMA
- https://blogs.oracle.com/mysql/post/explain-into-and-explain-for-schema-in-mysql-81-and-82

MySQL: EXPLAIN ANALYZE
- https://hackmysql.com/post/book-2/

MySQL EXPLAIN ANALYZE (no replies)

Edwin Desouza — Wed, 09 Aug 2023 23:43:35 +0000

MySQL EXPLAIN ANALYZE:
- https://hackmysql.com/post/book-2/

MySQL Deferred Join: A Deep Dive (no replies)

Edwin Desouza — Wed, 09 Aug 2023 23:28:31 +0000

MySQL Deferred Join: A Deep Dive
- https://hackmysql.com/post/deferred-join-deep-dive/

Why multi-valued index is not in possible keys in a dependent subquery (no replies)

ELON ZHOU — Fri, 03 Feb 2023 04:01:24 +0000

-- DDL
create table scholardata_journal
(
id int auto_increment primary key,
sci_if double null,
issn_list json default (_utf8mb4'[]') not null
);

create index issn_list_multi_value_index
on journal ((cast(`issn_list` as char(15) array)));

create table userlibrary_biblio
(
id int auto_increment primary key,
user_id int NOT NULL,
issn varchar(50) not null,

constraint userlibrary_biblio_user_id_ecab5d00_fk_auth_user_id
foreign key (user_id) references ivy.auth_user (id)
);

-- SELECT in subquery
select id,
issn,
(
IF(userlibrary_biblio.issn = '', null, (select sci_if
from scholardata_journal
where userlibrary_biblio.issn member of(scholardata_journal.issn_list)
limit 1))
) as sci_if
from userlibrary_biblio where user_id = 28;

---
Explain result:

-> Index lookup on userlibrary_biblio using userlibrary_biblio_user_id_ecab5d00_fk_auth_user_id (user_id=28) (cost=260.78 rows=254) (actual time=0.098..1.185 rows=254 loops=1)
-> Select #2 (subquery in projection; dependent)
-> Limit: 1 row(s) (cost=5875.65 rows=1) (actual time=19.485..19.486 rows=1 loops=84)
-> Filter: userlibrary_biblio.issn member of (scholardata_journal.issn_list) (cost=5875.65 rows=56274) (actual time=19.485..19.485 rows=1 loops=84)
-> Table scan on scholardata_journal (cost=5875.65 rows=56274) (actual time=0.005..13.855 rows=22979 loops=84)

MySQL Hypergraph Optimizer for HeatWave (no replies)

Edwin Desouza — Tue, 17 Jan 2023 15:20:37 +0000

MySQL Hypergraph Optimizer for HeatWave
- https://blogs.oracle.com/mysql/post/fast-query-performance-with-mysql-hypergraph-optimizer-for-heatwave

what does explain ref const, const means ? (no replies)

jongse park — Wed, 23 Nov 2022 06:00:50 +0000

I executed explain a select query.
the result shows two const, const for in a ref column.
what does two const, const means ?
does explain reads twice table ?

Please help me.

Set Theory in MySQL 8.0: UNION and Now INTERSECT and EXCEPT (no replies)

Edwin Desouza — Wed, 02 Nov 2022 16:25:28 +0000

https://www.percona.com/blog/set-theory-in-mysql-8-0-union-and-now-intersect-and-except/

Plugin For Obtaining Tokenized Query (no replies)

JustADude Tester — Wed, 19 Oct 2022 14:23:09 +0000

Dear Community,
I need for a private project to get from the mysql database any query, that is processed, in a tokenized form.

Example:
Select * from users where 1 = 1;
Desired Output:
["Select", "*", "from", "users", "where", "1", "=", "1"]

I started developing a PoC by implementing a simple query rewrite plugin. Currently I want to call the 'get_command' method of a MYSQL_THD object. However,
every time I try to compile my plugin it gives me the following error

'error: invalid use of incomplete type ‘class THD’'

I have included the following files in my plugin:
#include
#include
#include
#include
#include
#include
#include
#include

The following snippet is my code:

if (event_class == MYSQL_AUDIT_PARSE_CLASS)
{
const struct mysql_event_parse *event_parse=
static_cast(event);

if (event_parse->event_subclass == MYSQL_AUDIT_PARSE_POSTPARSE)
{
thd->get_command();
}
}

However, it still wont compile. What header file am I missing or better question why can't I access any variable or method of the MYSQL_THD object.

I hope this is the right place for this question and hope that you guys can help me :)

Why MySQL adding 1 when calculating the records per key? (no replies)

yufeng shen — Sun, 25 Sep 2022 01:16:34 +0000

When calculating the records per key, we see the MySQL using the "rec_per_key_t(tab->records()) / distinct_keys_est + 1;" ,my question is why it doesn't use the "rec_per_key_t(tab->records()) / distinct_keys_est ;" instead ,why it needs the "+ 1" here?

The code segment is below:

/*
Assume that the first key part matches 1% of the file
and that the whole key matches 10 (duplicates) or 1
(unique) records.
Assume also that more key matches proportionally more
records
This gives the formula:
records = (x * (b-a) + a*c-b)/(c-1)

b = records matched by whole key
a = records matched by first key part (1% of all records?)
c = number of key parts in key
x = used key parts (1 <= x <= c)
*/
rec_per_key_t rec_per_key;
if (keyinfo->has_records_per_key(
keyinfo->user_defined_key_parts - 1))
rec_per_key=
keyinfo->records_per_key(keyinfo->user_defined_key_parts - 1);
else
rec_per_key=
rec_per_key_t(tab->records()) / distinct_keys_est + 1;

https://github.com/mysql/mysql-server/blob/5.7/sql/sql_planner.cc#L556

Why MySQL adding 1 when calculating the records per key? (no replies)

yufeng shen — Sun, 25 Sep 2022 01:10:01 +0000

When calculating the records per key, we see the MySQL using the "rec_per_key_t(tab->records()) / distinct_keys_est + 1;" ,my question is why it doesn't use the "rec_per_key_t(tab->records()) / distinct_keys_est ;" instead ,why it needs the "+ 1" here?

The code segment is below:

/*
Assume that the first key part matches 1% of the file
and that the whole key matches 10 (duplicates) or 1
(unique) records.
Assume also that more key matches proportionally more
records
This gives the formula:
records = (x * (b-a) + a*c-b)/(c-1)

b = records matched by whole key
a = records matched by first key part (1% of all records?)
c = number of key parts in key
x = used key parts (1 <= x <= c)
*/
rec_per_key_t rec_per_key;
if (keyinfo->has_records_per_key(
keyinfo->user_defined_key_parts - 1))
rec_per_key=
keyinfo->records_per_key(keyinfo->user_defined_key_parts - 1);
else
rec_per_key=
rec_per_key_t(tab->records()) / distinct_keys_est + 1;

The code segment above can be found here:
https://github.com/mysql/mysql-server/blob/5.7/sql/sql_planner.cc#L556

MYSQL5.7:OPTIMIZER Contradict Explain (1 reply)

yijie fu — Wed, 07 Sep 2022 11:26:59 +0000

MYSQL5.7:OPTIMIZER Contradict Explain
example1:
Index：ALTER TABLE t_app_recommended_info ADD INDEX idx_uid_create_time_type (uid,create_time,type);
SQL：select * from t_app_recommended_info where uid = 1252057754179211264 and create_time >'2022-09-01 00:00:00' and type =1 and params like '%"couponId":12582268875%' and params like '%"hmsr":"saomaqudao"%';
explain:key-len=12

OPTIMIZER_TRACE:refine_plan: "pushed_index_condition": "((`t_app_recommended_info`.`type` = 1) and (`t_app_recommended_info`.`uid` = 1252057754179211264) and (`t_app_recommended_info`.`create_time` > '2022-09-01 00:00:00'))",
example2:
Index：ALTER TABLE t_app_recommended_info ADD INDEX idx_uid_type_createtime (uid,type,create_time);
SQL：select * from t_app_recommended_info where uid = 1252057754179211264 and create_time >'2022-09-01 00:00:00' and type =1 and params like '%"couponId":12582268875%' and params like '%"hmsr":"saomaqudao"%';
explain:key-len=16

OPTIMIZER_TRACE:refine_plan: "pushed_index_condition": "((`t_app_recommended_info`.`type` = 1) and (`t_app_recommended_info`.`uid` = 1252057754179211264) and (`t_app_recommended_info`.`create_time` > '2022-09-01 00:00:00'))",
example1 compair example2 [explain key-len] find type（4byte）not used，but OPTIMIZER_TRACE:refine_plan: find user index' type，help.

Need help with getting rid of filesort (1 reply)

Marvin Herbold — Fri, 24 Jun 2022 15:59:23 +0000

I have a pretty simple select query that the optimizer insists on using filesort for.

EXPLAIN SELECT
`id`, `displayName`, `order`
FROM
`someTable`
WHERE
`id` != 0
ORDER BY
`displayName`, `order`
LIMIT
123456, 1000

`id` is PK.

`displayName` and `order` have their own indexes.

I also have a ( `displayName`, `order` ) index as well as a ( `displayName`, `order`, `id` ) index.

The optimizer still insists on using filesort. If I add a FORCE INDEX to force it to use the ( `displayName`, `order` ) index, it works and runs much faster (50x faster). If I remove the WHERE clause, the filesort goes away. How can I properly set up this table / indexes so that I can have that where clause, and the optimizer would not use filesort?

Thanks for any help!

MySQL 8.0 EXPLAIN ANALYZE (no replies)

Edwin Desouza — Mon, 28 Feb 2022 04:37:40 +0000

MySQL EXPLAIN ANALYZE is an indispensable tool for understanding query execution because it breaks down the query execution stage of response time by measuring each step of the query execution plan. The information is illuminating, but the output is not intuitive: it requires practice and some understanding of how MySQL executes queries beyond the table join order shown by traditional EXPLAIN output. This blog post closely examines three different examples of EXPLAIN ANALYZE output.
https://hackmysql.com/post/book-2/

Startup was very slow (5 replies)

Charles Rodrigues — Sun, 09 Oct 2022 15:24:16 +0000

Hi,

I tried to open this bug ( https://bugs.mysql.com/bug.php?id=106269&thanks=2¬ify=67 ) that has been happening for a long time since a 5.7.x relase that I don't remember anymore, but it wasn't accepted I don't know why.

The team said that there are adjustments that can improve this reading time of the .ibd files, I've tried everything but until today it doesn't improve the time it just gets worse.

Does anyone know what can be done on windows so that MySQL does not take more than 1h to go up?

Thank you so much.

Optimizer in 8.0 in some cases not materializing subquery (1 reply)

Benjamin Peikes — Mon, 24 Jan 2022 22:45:19 +0000

We have a query that went from taking 2 seconds in MySQL 5.7 to 50+ seconds in 8.0. It appears it has to do with how the optimizer is handling some subqueries.

Example of query:

SELECT e.id, e.status_id, COUNT(e.id) AS cnt
FROM Events e
WHERE e.ent_id IN (1)
AND
NOT EXISTS
(
SELECT eam.account
FROM EventAccountMapping eam
WHERE
eam.event_id = e.id AND
eam.account NOT IN
(
SELECT uapt.account
FROM UserAccountPermissions uapt
WHERE uapt.user_id = 1
) /* Subquery 2 */
)

In 5.7, it was treating Subquery 2 as a subquery, and in 5.8, it's be treated as a dependent subquery, even though it is not dependent on the other tables

If I break this into 2 queries where the select from UserAccountPermissions saves the results in a temp table, and then use that temp table, it goes from 50 seconds to 2. If I actually generate the results fo subquery 2, and inline the results ( 'aaa', 'bbbb', 'cccc', etc), the query goes down to less than one second.

In 5.8, I moved the subquery 2 to a CTE, and that doesn't resolve the issue either. Is there a way to force a CTE to get materialized?

Running sql in server is too much slower then laptop (3 replies)

Herems Jing — Sun, 14 Nov 2021 03:21:28 +0000

I use the same db data( import to server from my laptop's mysqldump), same mysql version 5.7.0,
The server's CPU is Xeon E5-2670 v3 2.3GHz, with 8G RAM, while laptop's is just i7-8565U 1.8GHz，with 8G RAM.
But running same sql in server is much slower then laptop.
For example, to run this sql "update a , b set a.tid=b.tid, a.tname=b.tname where a.m=b.m and (a.tid<>b.tid or a.tname<>b.tname)", server used 756 seconds, but only 2.7 seconds in my laptop. Table a has 550K rows data. Table b has 207 rows data.
It's very strange and I don't know how to optimize in server mysql. Our application can't run well in server because it's too slow.

Could you help me identify what reasons may cause this problem?

Query optimization on LIKE (1 reply)

Sing Lau — Mon, 04 Oct 2021 19:08:41 +0000

We have a simple query like this in mysql:

SELECT

ps.productType,

COUNT(DISTINCT ps.some_other_id)

FROM

product_sum ps

WHERE ps.customerId = 'abc'

AND ps.partitionId = 'bbc'

AND ps.orderId LIKE "%123456%"

GROUP BY ps.productType;

orderId field could be one id or multiple ids concat in this format: 123456,234567,345678

We can't remove the leading % in the LIKE condition since the orderId could have multiple ids in the string. Mysql is not using indexing on orderId column since it has NULL value. Tried the FullText index but it cannot be used in partitioned table and also not able to combine with other indexes. Is there any other way it can be optimized? Please advise. Thank you.

Run ANALYZE TABLE — Do Not Rely on InnoDB's Automatic Recalculation of Statistics (no replies)

Edwin Desouza — Wed, 08 Sep 2021 15:52:00 +0000

http://oysteing.blogspot.com/2021/09/run-analyze-table-do-not-rely-on.html

https://mysql.wisborg.dk/2021/08/22/automatic-update-of-innodb-persistent-statistics-never-triggers/

MySQL: EXPLAINing the Different EXPLAINS (no replies)

Edwin Desouza — Thu, 19 Aug 2021 20:57:41 +0000

https://elephantdolphin.blogspot.com/2021/08/explaining-different-explains-in-mysql.html

Multi-valued index is not in possible keys when there is a subquery (1 reply)

Jinlin Yang — Mon, 04 Oct 2021 18:55:56 +0000

If we table contains some column based index and also some multi-valued index for a json column, then the query optimizer behaves differently depending on if subquery is used.

Specifically when a subquery is used then the multi-valued index is NOT listed as a possible keys in the output of the explain command. However, if we change the query not to use any subquery, then the multi-valued index is among the possible keys.

Does anyone know how to work around this issue?

I have reported a bug at https://bugs.mysql.com/bug.php?id=104388.

Below is the steps to reproduce the problem.

mysql> STATUS;
--------------
mysql Ver 8.0.25 for macos11.3 on x86_64 (Homebrew)

Connection id: 18
Current database: myapp
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.25 Homebrew
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /tmp/mysql.sock
Binary data as: Hexadecimal
Uptime: 41 min 5 sec

Threads: 8 Questions: 958 Slow queries: 0 Opens: 1005 Flush tables: 3 Open tables: 912 Queries per second avg: 0.388
--------------

mysql>
mysql> DROP TABLE IF EXISTS test_json_index;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE test_json_index
-> (
-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> OrgID INT NOT NULL,
-> BinID INT NOT NULL,
-> DataBlob json NOT NULL
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE test_json_index
-> ADD INDEX (OrgID),
-> ADD INDEX (BinID),
-> ADD INDEX indexA (( CAST(DataBlob->'$.a' AS UNSIGNED INTEGER ARRAY) )),
-> ADD INDEX indexB (( CAST(DataBlob->'$.b' AS UNSIGNED ) ));
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql>
mysql> INSERT INTO test_json_index (OrgID, BinID, DataBlob)
-> VALUES(
-> 1, 1, "{\"a\":[1, 2, 3], \"b\": 1}"
-> );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test_json_index (OrgID, BinID, DataBlob)
-> VALUES(
-> 2, 2, "{\"a\":[4, 5, 6], \"b\": 2}"
-> );
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SHOW INDEX FROM test_json_index;
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------------------------------------------------------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------------------------------------------------------------+
| test_json_index | 0 | PRIMARY | 1 | id | A | 1 | NULL | NULL | | BTREE | | | YES | NULL |
| test_json_index | 1 | OrgID | 1 | OrgID | A | 1 | NULL | NULL | | BTREE | | | YES | NULL |
| test_json_index | 1 | BinID | 1 | BinID | A | 1 | NULL | NULL | | BTREE | | | YES | NULL |
| test_json_index | 1 | indexA | 1 | NULL | A | 1 | NULL | NULL | YES | BTREE | | | YES | cast(json_extract(`DataBlob`,_utf8mb4\'$.a\') as unsigned array) |
| test_json_index | 1 | indexB | 1 | NULL | A | 1 | NULL | NULL | YES | BTREE | | | YES | cast(json_extract(`DataBlob`,_utf8mb4\'$.b\') as unsigned) |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------------------------------------------------------------+
5 rows in set (0.00 sec)

mysql>
mysql> -- Filter on OrgID and DataBlob.a with a subquery, why is indexA missing in possible keys?
mysql> EXPLAIN SELECT *
-> FROM (SELECT * from test_json_index where OrgID = 1) as tmp
-> WHERE 1 member of(tmp.DataBlob->'$.a');
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | test_json_index | NULL | ref | OrgID | OrgID | 4 | const | 1 | 100.00 | Using where |
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>
mysql> -- Filter on OrgID and DataBlob.a without a subquery
mysql> EXPLAIN SELECT *
-> FROM test_json_index
-> WHERE 1 member of(DataBlob->'$.a') AND OrgID = 1;
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | test_json_index | NULL | ref | OrgID,indexA | OrgID | 4 | const | 1 | 100.00 | Using where |
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>
mysql> -- Filter on OrgID and DataBlob.b with a subquery, why is indexB missing in possible keys?
mysql> EXPLAIN SELECT *
-> FROM (SELECT * from test_json_index where OrgID = 1) as tmp
-> WHERE 1 = CAST(DataBlob->'$.b' AS UNSIGNED);
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | test_json_index | NULL | ref | OrgID | OrgID | 4 | const | 1 | 100.00 | Using where |
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>
mysql> -- Filter on OrgID and DataBlob.b without a subquery
mysql> EXPLAIN SELECT *
-> FROM test_json_index
-> WHERE 1 = CAST(DataBlob->'$.b' AS UNSIGNED) AND OrgID = 1;
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | test_json_index | NULL | ref | OrgID,indexB | OrgID | 4 | const | 1 | 50.00 | Using where |
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>
mysql> -- Filter on OrgID and BinID with a subquery, index on BinID is in possible keys
mysql> EXPLAIN SELECT *
-> FROM (SELECT * from test_json_index where OrgID = 1) as tmp
-> WHERE 1 = tmp.BinID;
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | test_json_index | NULL | ref | OrgID,BinID | OrgID | 4 | const | 1 | 50.00 | Using where |
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>
mysql> -- Filter on OrgID and BinID without a subquery
mysql> EXPLAIN SELECT *
-> FROM test_json_index
-> WHERE 1 = BinID AND OrgID = 1;
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | test_json_index | NULL | ref | OrgID,BinID | OrgID | 4 | const | 1 | 50.00 | Using where |
+----+-------------+-----------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Disable caching in MYSQL for True performance testings? (1 reply)

Sing Lau — Sun, 11 Jul 2021 23:21:42 +0000

We are trying to optimize some of the slow sql queries. When we first run the query, it's about XX secs, but after the first run, it only takes less than 1 sec for running the same query. It's really hard to compare the results for performance testing or query tunning.

We have disabled the query cache in RDS parameter groups by setting the query cache type,size and limit to 0. And I have set the innodb buffer pool size to the minimum value and the innodb_old_page_pt to 5. But it's still acting the same -- after the first run, it will be much faster. Unless we have to restart the server or wait for couple of hours or days, then it will show the XX secs again.

Can anyone please suggest me how can I disable the caching in MySQL so that we can show the improvement on the query tunning? Thank you in advance.

Documentation regarding operator precedence incorrect for LIKE, REGEXP, IN? (no replies)

Justin Ng — Sat, 12 Jun 2021 19:29:04 +0000

I'm looking at MySQL 5.7,
https://dev.mysql.com/doc/refman/5.7/en/operator-precedence.html

It says the following have the same precedence,

```
= (comparison), <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
```

It also says,

> For operators that occur at the same precedence level within an expression, evaluation proceeds left to right

This would suggest that `a = b LIKE c` should be parsed as `(a = b) LIKE c`.
However, it is actually parsed as `a = (b LIKE c)`

The reason is because of the following,
```
predicate:
...
| bit_expr LIKE simple_expr opt_escape
...
| bit_expr
```

```
bool_pri:
...
| bool_pri comp_op predicate %prec EQ
...
| predicate
```

https://github.com/mysql/mysql-server/blob/5c8c085ba96d30d697d0baa54d67b102c232116b/sql/sql_yacc.yy#L9376

https://github.com/mysql/mysql-server/blob/5c8c085ba96d30d697d0baa54d67b102c232116b/sql/sql_yacc.yy#L9308

-----

In order to parse as `('a' = 'a') LIKE '0'`, we would need a rule like,

```
predicate:
...
| bool_pri LIKE simple_expr opt_escape
```

because `'a' = 'a'` is a `bool_pri`.

However, there is no such rule.

How to optimizate COUNT query with multiple LEFT JOIN (2 replies)

Ivan Stoqnov — Tue, 01 Jun 2021 18:19:05 +0000

I have query that select filters for current category and show how many products in each filter are Atm i have 20k products and 10k filters for test that take around 1sec. to execute that is not to good for me is there anything i can do to optimize this query ?

I found that in WHERE caluse

AND sd.filter_group_id = fd.filter_group_id
AND p.status = 1

increase executing time a lot if u move them on the LEFT JOIN like this

LEFT JOIN oc_product p ON p.product_id = p2c.product_id AND p.status = 1
LEFT JOIN oc_sd_filter sd ON sd.category_id = p2c.category_id AND sd.status = 1 AND sd.filter_group_id = fd.filter_group_id

Executing time become around 0.0600 but results are not same

How to handle this problem ?

Example of my query with selected filters (pf tables are added dynamically depend of filter groups )

SELECT SQL_NO_CACHE sd.filter_group_id,
fgd.name AS group_name,
pf.filter_id AS filter_id,
fd.name,
COUNT(DISTINCT p2c.product_id) AS total
FROM oc_product_to_category p2c
LEFT JOIN oc_product_filter pf5 ON pf5.product_id = p2c.product_id
LEFT JOIN oc_product_filter pf8 ON pf8.product_id = p2c.product_id
LEFT JOIN oc_product_filter pf10 ON pf10.product_id = p2c.product_id
LEFT JOIN oc_product_filter pf3 ON pf3.product_id = p2c.product_id
LEFT JOIN oc_product_filter pf9 ON pf9.product_id = p2c.product_id
LEFT JOIN oc_product_filter pf6 ON pf6.product_id = p2c.product_id
LEFT JOIN oc_product p ON p.product_id = p2c.product_id
LEFT JOIN oc_product_filter pf ON pf.product_id = p2c.product_id
LEFT JOIN oc_filter f ON pf.filter_id = f.filter_id
LEFT JOIN oc_filter_description fd ON pf.filter_id = fd.filter_id
LEFT JOIN oc_filter_group_description fgd
ON fd.filter_group_id = fgd.filter_group_id
LEFT JOIN oc_sd_filter sd
ON sd.category_id = p2c.category_id AND sd.status = 1
WHERE p2c.category_id = '64'
AND sd.filter_group_id = fd.filter_group_id
AND p.status = 1
AND pf5.filter_id IN (33)
AND pf8.filter_id IN (47,141,143,144,145)
AND pf10.filter_id IN (118,120,121,122,123,128,129,130,134,138,157,163)
AND pf3.filter_id IN (9,11)
AND pf9.filter_id IN (57,58,59,60,61,94,95)
AND pf6.filter_id IN (116)
GROUP BY fd.filter_id, fd.filter_group_id
ORDER BY sd.sort_order ASC,
(CASE WHEN fgd.custom_order = 0 THEN f.sort_order END) ASC,
(CASE WHEN fgd.custom_order = 1 THEN COUNT(p2c.product_id) END) DESC

Here are my table structure

CREATE TABLE `oc_product` (
`product_id` int(11) NOT NULL,
`model` varchar(64) NOT NULL,
`sku` varchar(64) NOT NULL,
`upc` varchar(12) NOT NULL,
`ean` varchar(14) NOT NULL,
`jan` varchar(13) NOT NULL,
`isbn` varchar(17) NOT NULL,
`mpn` varchar(64) NOT NULL,
`location` varchar(128) NOT NULL,
`quantity` int(4) NOT NULL DEFAULT 0,
`manufacturer_id` int(11) NOT NULL,
`price` decimal(15,4) NOT NULL DEFAULT 0.0000,
`tax_class_id` int(11) NOT NULL,
`date_available` date NOT NULL DEFAULT '0000-00-00',
`sort_order` int(11) NOT NULL DEFAULT 0,
`status` tinyint(1) NOT NULL DEFAULT 0,
`date_added` datetime NOT NULL,
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE `oc_product`
ADD PRIMARY KEY (`product_id`),
ADD KEY `model` (`model`),
ADD KEY `manufacturer_id` (`manufacturer_id`),
ADD KEY `sort_order` (`sort_order`),
ADD KEY `status` (`status`) USING BTREE;

CREATE TABLE `oc_product_to_category` (
`product_id` int(11) NOT NULL,
`category_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `oc_product_to_category`
ADD PRIMARY KEY (`product_id`,`category_id`),
ADD KEY `category_id` (`category_id`);

CREATE TABLE `oc_product_filter` (
`product_id` int(11) NOT NULL,
`filter_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `oc_product_filter`
ADD PRIMARY KEY (`product_id`,`filter_id`);

CREATE TABLE `oc_sd_filter` (
`id` int(11) NOT NULL,
`category_id` int(11) NOT NULL,
`filter_group_id` int(11) NOT NULL,
`status` int(11) NOT NULL,
`sort_order` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-
ALTER TABLE `oc_sd_filter`
ADD PRIMARY KEY (`id`),
ADD KEY `filter-category` (`category_id`,`filter_group_id`);

CREATE TABLE `oc_filter` (
`filter_id` int(11) NOT NULL,
`filter_group_id` int(11) NOT NULL,
`sort_order` int(3) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE `oc_filter`
ADD PRIMARY KEY (`filter_id`);

CREATE TABLE `oc_filter_description` (
`filter_id` int(11) NOT NULL,
`language_id` int(11) NOT NULL,
`filter_group_id` int(11) NOT NULL,
`name` varchar(64) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE `oc_filter_description`
ADD PRIMARY KEY (`filter_id`,`language_id`),
ADD KEY `filter` (`filter_group_id`);

CREATE TABLE `oc_filter_group_description` (
`filter_group_id` int(11) NOT NULL,
`language_id` int(11) NOT NULL,
`name` varchar(64) NOT NULL,
`level` int(11) NOT NULL,
`custom_order` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE `oc_filter_group_description`
ADD PRIMARY KEY (`filter_group_id`,`language_id`);

pf10.filter_id IN (118,120,121,122,123,128,129,130,134,138,157,163)

pf10 represents filter_group

IN (118,120.....) represents selected filters for current filter_group

for each selected filter that is in different filter_group i do LEFT JOIN, then all filters from same group go in same IN()

Need help with tracing how identifiers are parsed; dot-reserved vs dot-space-reserved (1 reply)

Justin Ng — Tue, 25 May 2021 12:21:54 +0000

The following are subtly different.

+ `CREATE TABLE s.SELECT (X INT);`
+ `CREATE TABLE s. SELECT (X INT);`

The first statement will create a table named `SELECT`, under the schema `s`.

The second statement will get a parse error.

> 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 'SELECT (X INT)' at line 1

-----

The difference is whether a space exists after the identifier separator or not.

I tried looking into the MySQL source code but could not find where this check is performed and what is raising the error.

I've looked at the `table_ident` rule in `sql_yacc.yy`, and states `MY_LEX_IDENT_SEP, MY_LEX_START, MY_LEX_IDENT` in `sql_lex.cc`. I've also looked at `Table_ident` in `sql_lex.h/sql_lex.cc`.

However, I could not figure it out.

Could someone point me in the right direction, short of having me compile and debug from source?

MySQL Invisible Column (no replies)

Edwin Desouza — Fri, 22 Jan 2021 19:48:31 +0000

MySQL Invisible Column – introduction
https://lefred.be/content/mysql-invisible-column-part-i/

MySQL Invisible Column is important for InnoDB Storage Engine
- https://lefred.be/content/mysql-invisible-column-part-ii/

MySQL Invisible Column is important for InnoDB Cluster
- https://lefred.be/content/mysql-invisible-column-part-iii/

mySQL 8 - view query not using outer predicate (7 replies)

Jack Crawford — Tue, 12 Jan 2021 04:34:02 +0000

It is my understanding from https://dev.mysql.com/doc/refman/8.0/en/derived-condition-pushdown-optimization.html the predicate in an outer query should be used in the view query. Either I'm misreading this or the solution escapes me.

I'm issuing this query:

explain select * from order_item_oi_only where line_item_auto_key = 1112121

and getting:
```
1 PRIMARY ALL 143159 100.00
2 DERIVED i ALL 143159 100.00 Using temporary; Using filesort
2 DERIVED o eq_ref PRIMARY,PARTNER_ACCOUNT_ID_IDX,ACCOUNT_ID_IDX,USER_ID_IDX,ORDER_ENTRY_TIME_IDX,CUST_REF_NUM_IDX,USER_COMMENT_IDX PRIMARY 8 wtg_ecomm_dev.i.ORDER_ID 1 100.00
```

Query is executing the following view:

CREATE OR REPLACE VIEW `Order_Item_oi_only` AS

SELECT i.line_item_auto_key, JSON_OBJECT(
'accountId', o.ACCOUNT_ID,
'buildDate', o.ORDER_BILLED,
'billingType', o.BILLING_TYPE,
'clientInfo', o.USER_COMMENT,
'customerRefNum', o.CUST_REF_NUM,
'date', o.ORDER_ENTRY_TIME,
'deliveryNotificationType', o.DELIVERY_NOTIFICATION_TYPE,
'fulfillmentEmail', o.FULFILLMENT_EMAIL,
'id', o.ORDER_AUTO_KEY,
'merchantAccountTransId', o.MERCHANT_ACCT_TRANS_ID,
'newDownloadsAvailable', o.NEW_DOWNLOADS_AVAIL,
'notificationMethod', o.NOTIFY_METHOD,
'orderComments', o.ORDER_COMMENTS,
'partnerAccountId', o.PARTNER_ACCOUNT_ID,
'partnerUserAccountId', o.PARTNER_USER_ACCOUNT_ID,
'partnerUserId', o.PARTNER_USERID,
'paymentMethod', o.PAYMENT_METHOD,
'sourceApp', o.SOURCE_APP,
'status', o.ORDER_STATUS,
'totalCost', o.TOTAL_COST,
'userid', o.USER_ID,

'lineItems', JSON_ARRAYAGG(
JSON_OBJECT(
'apn', i.APN,
'canBeGenerated', i.CAN_BE_GENERATED,
'completeTime', i.COMPLETE_TIME,
'confirmAllAddlLotsIn', i.CONF_ALL_ADDL_LOTS_IN,
'confirmIsCBRSOPA', i.CONF_IS_CBRS_OPA,
'confirmIsClaimOnParcel', i.CONF_IS_CLAIM_ON_PARCEL,
'confirmIsLOMC', i.CONF_IS_LOMC,
'confirmIsSFHA', i.CONF_IS_SFHA,
'countyFIPS', i.COUNTY_FIPS,
'customerRefNum', i.CUST_REF_NUM,
'dateAssigned', i.ASSIGNED_TIME,
'dateDue', i.DUE_TIME,
'emitPricingOnReport', i.EMIT_PRICING_ON_REPORT,
'fundId', i.FUND_ID,
'fundingType', i.FUNDING_TYPE,
'generationStatus', i.GENERATION_STATUS,
'id', i.LINE_ITEM_AUTO_KEY,
'issueCertificateToAddr', i.ISSUE_CERT_TO_ADDR,
'issueCertificateToName', i.ISSUE_CERT_TO,
'legalDescription', i.LEGAL_DESC,
'muniFIPS', i.MUNI_FIPS,
'orderId', i.ORDER_ID,
'owner', i.OWNER,
'parcelBlock', i.PARCEL_BLOCK,
'parcelDistrict', i.PARCEL_DISTRICT,
'parcelKey', i.PARCEL_KEY,
'parcelLot', i.PARCEL_LOT,
'parcelQualifier', i.PARCEL_QUALIFY,
'parsedMisc', i.PARSED_MISC,
'price', i.PRICE,
'processStartTime', i.PROCESS_START_TIME,
'productId', i.PRODUCT_ID,
'providerId', i.PROVIDER_ID,
'providerOKToPay', i.PROVIDER_OK_TO_PAY,
'quantity', i.QUANTITY,
'stateFIPS', i.STATE_FIPS,
'status', i.ITEM_STATUS,
'statusReason', i.ITEM_STATUS_REASON,
'streetAddress', i.STREET_ADDR,
'taxSearchIncludeUtils', i.TAX_SEARCH_INCLUDE_UTILS,
'townId', i.TOWN_ID,
'userComment', i.USER_COMMENT,
'workerId', i.WORKER_ID
)
)
) as order_json
FROM LINE_ITEMS i FORCE INDEX (PRIMARY)
LEFT JOIN ORDERS o ON o.order_auto_key = i.order_id
group by o.order_auto_key
where both the following are primary keys on their respective tables:

o.order_auto_key i.line_item_auto_key

I'm running mySQL 8.0.22

Could sure use some help with this one. Thanks.

MySQL Comparing INTs and CHARs (no replies)

Edwin Desouza — Mon, 24 Aug 2020 20:56:34 +0000

https://elephantdolphin.blogspot.com/2020/08/mysql-comparing-ints-and-chars.html

MySQL 8.0 Indexes, Histograms, and Other Ways to Speed Up Your Queries (no replies)

Edwin Desouza — Wed, 05 Aug 2020 01:57:45 +0000

MySQL 8.0 Indexes, Histograms, and Other Ways to Speed Up Your Queries
- https://www.slideshare.net/davestokes/mysql-80-indexes-histograms-and-other-ways-to-speed-up-your-queries

MySQL: Using SKIP LOCK For Queue Processing (no replies)

Edwin Desouza — Mon, 03 Aug 2020 16:00:27 +0000

SKIP LOCKED and NOWAIT:
- http://mysqlserverteam.com/mysql-8-0-1-using-skip-locked-and-nowait-to-handle-hot-rows/
- https://www.percona.com/blog/2020/08/03/using-skip-lock-for-queue-processing-in-mysql/