Hacktoberfest 2026: the issues maintainers tagged for October, open and beginner-friendly. Browse Hacktoberfest issues

优化建立索引有问题

Open
  • Is something wrong?
#39 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
3/5
Estimated time
1-2 days
Newbie friendliness
45/100
Issue type
Bug
Clarity
Mostly clear
Activity status
Stale
Tech stack
c
Domain
databases

Research direction

Reproduce the reported SQLAdvisor index recommendation with the three tables and query from the issue, then trace the index-suggestion entry point that produces the ALTER statement for table c. The fix is complete when the recommendation contains user_id only once and the generated ALTER statement executes without the duplicate-column error.

Written by the indexing model from the issue text.

Description

有a/b/c三张表
create table a (id int auto_increment,seller_id bigint,seller_name varchar(100) collate utf8_bin ,gmt_create varchar(30),primary key(id));
create table b (id int auto_increment,seller_name varchar(100),user_id varchar(50),user_name varchar(100),sales bigint,gmt_create varchar(30),primary key(id));
create table c (id int auto_increment,user_id varchar(50),order_id varchar(100),state bigint,gmt_create varchar(30),primary key(id));

使用sql
select a.seller_id,a.seller_name,b.user_name,c.state
from a,b,c
where a.seller_name=b.seller_name
and b.user_id=c.user_id
and c.user_id=17
and a.gmt_create BETWEEN DATE_ADD(NOW(), INTERVAL - 600 MINUTE)
AND DATE_ADD(NOW(), INTERVAL 600 MINUTE)
order by a.gmt_create

然后在建议c表时出现如下优化语句：
2017-07-19 11:15:53 4364 [Note] 第31步：开始验证 字段user_id是不是主键。表名:c
2017-07-19 11:15:53 4364 [Note] show index from c where Key_name = 'PRIMARY' and Column_name ='user_id' and Seq_in_index = 1
2017-07-19 11:15:53 4364 [Note] 第32步：字段user_id不是主键。表名:c
2017-07-19 11:15:53 4364 [Note] 第33步：开始验证 字段user_id是不是主键。表名:c
2017-07-19 11:15:53 4364 [Note] show index from c where Key_name = 'PRIMARY' and Column_name ='user_id' and Seq_in_index = 1
2017-07-19 11:15:53 4364 [Note] 第34步：字段user_id不是主键。表名:c
2017-07-19 11:15:53 4364 [Note] 第35步：开始验证表中是否已存在相关索引。表名:c, 字段名:user_id, 在索引中的位置:1
2017-07-19 11:15:53 4364 [Note] show index from c where Column_name ='user_id' and Seq_in_index =1
2017-07-19 11:15:53 4364 [Note] 第36步：开始验证 字段user_id是不是主键。表名:c
2017-07-19 11:15:53 4364 [Note] show index from c where Key_name = 'PRIMARY' and Column_name ='user_id' and Seq_in_index = 1
2017-07-19 11:15:53 4364 [Note] 第37步：字段user_id不是主键。表名:c
2017-07-19 11:15:53 4364 [Note] 第38步：开始验证表中是否已存在相关索引。表名:c, 字段名:user_id, 在索引中的位置:2
2017-07-19 11:15:53 4364 [Note] show index from c where Column_name ='user_id' and Seq_in_index =2
2017-07-19 11:15:53 4364 [Note] 第39步：开始输出表c索引优化建议:
2017-07-19 11:15:53 4364 [Note] Create_Index_SQL：alter table c add index idx_user_id_user_id(user_id,user_id)

这个建议有两个user_id，但是语句执行错误的
[Err] 1060 - Duplicate column name 'user_id' 。
麻烦看一下。

Dominant language
C
Stars
5.6k
Forks
1.2k
No merged PRs in 30d

Getting set up

We have not checked this project's setup files yet. Start from its README, and see our first-contribution guide for the general steps.

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from Meituan-Dianping/SQLAdvisor

All issues in Meituan-Dianping/SQLAdvisor

Similar issues

More C issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.