Why does query become very slow with multiple indexes even I don't use it

Question

I am using TiDB to do some testing.

I created a table as below

CREATE TABLE users(
 id BIGINT PRIMARY KEY NOT NULL,
 updated BIGINT NOT NULL
)

I loaded around 100,000,000 rows into this table with 2 indexes

CREATE INDEX hash_index USING HASH ON users (id);
CREATE INDEX btree_index USING BTREE ON users (updated);

I found query speed became very slow, it would take a few seconds

Query sql is below. I only used the first index.

SELECT * FROM users where id=1999;

I solved this slow issue by deleting the second index updated

I thought second index cause this issue.

I was just wondering how it happened?

Also, given that id is a PRIMARY KEY, I'm struggling to understand the point of the hash_index — Strawberry, Nov 13 '20 at 07:43

score 0 · Answer 1 · answered Dec 04 '20 at 01:54

What's your TiDB version?
The id column in the USERS table is the primary key. The execution plan should Point_Get_1. I think the index should have no effect.

+-------------+---------+---------+------+---------------+----------------------------------------------------------------+---------------+--------+------+ | id | estRows | actRows | task | access object | execution info | operator info | memory | disk | +-------------+---------+---------+------+---------------+----------------------------------------------------------------+---------------+--------+------+ | Point_Get_1 | 1.00 | 1 | root | table:users | time:1.302907ms, loops:2, Get:{num_rpc:1, total_time:1.2536ms} | handle:10 | N/A | N/A | +-------------+---------+---------+------+---------------+----------------------------------------------------------------+---------------+--------+------+

I think you could check "explain analyze SELECT * FROM users where id=1999;". Is the execution plan of the SQL same？

Why does query become very slow with multiple indexes even I don't use it

1 Answers1