Differences in SQL Syntax Between MySQL and TiDB

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: Mysql中SQL语法和TiDB存在差异

| username: 会飞的企鹅

[Test Environment for TiDB] Testing
[TiDB Version] 6.5
[Reproduction Path] Execute SQL
[Encountered Issue: Problem Phenomenon and Impact]
[Resource Configuration]
[Attachment: Screenshot/Log/Monitoring]


As shown in the figure above, I created a composite index for the table

Executing explain found that the index did not take effect

After adding USE INDEX in the SQL statement, explain found that the index took effect. In MySQL, the index can still take effect without specifying USE INDEX. Could you please explain the reason for the issue? Thank you.

| username: wzf0072 | Original post link

How is the data distributed? select project_id, count(1) from table group by project_id order by 2 desc, is 3.31 million records inaccurate statistical information?

| username: 会飞的企鹅 | Original post link

3.31 million is the number of indexes, right?

| username: tidb狂热爱好者 | Original post link

If you create a time index, it will always go the wrong way.

| username: 会飞的企鹅 | Original post link

Hello, the composite index does indeed use a datetime type field. Is this related?

| username: tidb菜鸟一只 | Original post link

There are differences between the two optimizers.

| username: 考试没答案 | Original post link

Which is faster, using an index or not using an index? Which is better?

| username: 考试没答案 | Original post link

MySQL has an ICP feature, and TiDB also has the ability to push down to TiKV. With different optimization algorithms, the chosen paths will be different.

| username: 会飞的企鹅 | Original post link

Without using the index, the query takes more than ten seconds. After adding the index, the query time is around one second.

| username: 考试没答案 | Original post link

Could you share the table structure for us to take a look?

| username: 考试没答案 | Original post link

Fix the execution plan. TiDB supports fixed execution plans. Or add this index in the program.

| username: 会飞的企鹅 | Original post link

The image you provided is not accessible. Please provide the text you need translated.

| username: 考试没答案 | Original post link

DDL? The table creation statement.

| username: 会飞的企鹅 | Original post link

CREATE TABLE turnstile_inout_record (
id bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘Primary Key’,
person_id bigint(20) NOT NULL DEFAULT ‘0’,
org_id bigint(20) NOT NULL DEFAULT ‘0’,
project_id bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘Project ID’,
turnstile_id varchar(20) NOT NULL DEFAULT ‘’,
area_id bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘Area ID’,
current_area_id varchar(100) NOT NULL DEFAULT ‘’,
card_number varchar(50) NOT NULL DEFAULT ‘’ COMMENT ‘Card Number’,
pass_time datetime DEFAULT NULL COMMENT ‘Pass Time’,
access_dict int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘In/Out’,
create_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT ‘Creation Time’,
update_time datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘Update Time’,
is_removed tinyint(1) NOT NULL DEFAULT ‘0’ COMMENT ‘Logical Deletion’,
result_photo varchar(255) DEFAULT NULL,
PRIMARY KEY (id) /*T![clustered_index] CLUSTERED */,
KEY idx_project_and_pass (project_id,pass_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=13994279 COMMENT=‘Turnstile In/Out Log’;

| username: 特雷西-迈克-格雷迪 | Original post link

It’s normal. First, analyze the table to see if the index can be used reasonably.

| username: 会飞的企鹅 | Original post link

If you don’t use USE INDEX, the index cannot be used.

| username: ealam_小羽 | Original post link

The person above wants to confirm if this is the situation:

| username: TiDBer_刚 | Original post link

It is estimated that the statistical information needs to be recollected.