query slower after migrated to tidb

[gutongjiang]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.

the same sql query slower than mysql :
select fid, floginname, fnickname, floginpassword, ftradepassword, ftelephone, femail, frealname, fidentityno, fidentitytype, fgoogleauthenticator, fgoogleurl, fstatus, fhasrealvalidate, fhasrealvalidatetime, fistelephonebind, fismailbind, fgooglebind, fupdatetime, fareacode, version, fintrouid, finvalidateintrocount, fiscny, fiscoin, fbirth, flastlogintime, fregistertime, ftradepwdtime, fshowid, flastip, fplatform, fcarruertype, fcarruernum, fleadercode, fleaderinvitedcode, fleaderstatus from f_user where floginpassword = '3Eg+gKegvZ73HYz5c2c5JA==' and ftelephone = '13881964008';

2. What did you expect to see?

3. What did you see instead?

mysql about 0.01sec
tidb about 11.00 sec

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

tidb-server -V
Release Version: v2.1.0-rc.1-7-g38c939f
Git Commit Hash: 38c939f4f95c802d85a5b6fd098a7a33daf0937f
Git Branch: master
UTC Build Time: 2018-08-27 01:53:09
GoVersion: go version go1.10.2 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false

tikv-server --version
TiKV
Release Version: 2.1.0-rc.1
Git Commit Hash: 2174a98214e904dd9343817f001db1167614bd4c
Git Commit Branch: master
UTC Build Time: 2018-09-13 08:34:29
Rust Version: rustc 1.29.0-nightly (4f3c7a472 2018-07-17)

explain select fid, floginname, fnickname, floginpassword, ftradepassword, ftelephone, femail, frealname, fidentityno, fidentitytype, fgoogleauthenticator, fgoogleurl, fstatus, fhasrealvalidate, fhasrealvalidatetime, fistelephonebind, fismailbind, fgooglebind, fupdatetime, fareacode, version, fintrouid, finvalidateintrocount, fiscny, fiscoin, fbirth, flastlogintime, fregistertime, ftradepwdtime, fshowid, flastip, fplatform, fcarruertype, fcarruernum, fleadercode, fleaderinvitedcode, fleaderstatus from f_user where floginpassword = '3Eg+gKegvZ73HYz5c2c5JA==' and ftelephone = '13881964008'\G;
*************************** 1. row ***************************
id: Projection_4
count: 0.79
task: root
operator info: bae_test.f_user.fid, bae_test.f_user.floginname, bae_test.f_user.fnickname, bae_test.f_user.floginpassword, bae_test.f_user.ftradepassword, bae_test.f_user.ftelephone, bae_test.f_user.femail, bae_test.f_user.frealname, bae_test.f_user.fidentityno, bae_test.f_user.fidentitytype, bae_test.f_user.fgoogleauthenticator, bae_test.f_user.fgoogleurl, bae_test.f_user.fstatus, bae_test.f_user.fhasrealvalidate, bae_test.f_user.fhasrealvalidatetime, bae_test.f_user.fistelephonebind, bae_test.f_user.fismailbind, bae_test.f_user.fgooglebind, bae_test.f_user.fupdatetime, bae_test.f_user.fareacode, bae_test.f_user.version, bae_test.f_user.fintrouid, bae_test.f_user.finvalidateintrocount, bae_test.f_user.fiscny, bae_test.f_user.fiscoin, bae_test.f_user.fbirth, bae_test.f_user.flastlogintime, bae_test.f_user.fregistertime, bae_test.f_user.ftradepwdtime, bae_test.f_user.fshowid, bae_test.f_user.flastip, bae_test.f_user.fplatform, bae_test.f_user.fcarruertype, bae_test.f_user.fcarruernum, bae_test.f_user.fleadercode, bae_test.f_user.fleaderinvitedcode, bae_test.f_user.fleaderstatus
*************************** 2. row ***************************
id: └─TableReader_7
count: 0.79
task: root
operator info: data:Selection_6
*************************** 3. row ***************************
id: └─Selection_6
count: 0.79
task: cop
operator info: eq(bae_test.f_user.floginpassword, "3Eg+gKegvZ73HYz5c2c5JA=="), eq(bae_test.f_user.ftelephone, "13881964008")
*************************** 4. row ***************************
id: └─TableScan_5
count: 144927.00
task: cop
operator info: table:f_user, range:[-inf,+inf], keep order:false

[shenli]

@gutongjiang Thanks for your feedback! Have you analyzed that table?

[gutongjiang]

@shenli What do you mean, should I provide more informations

[shenli]

• the create table statement for the involved tables

show create table ${involved_table_1}\G
show create table ${involved_table_2}\G
...

• the statistics data of the involved tables

curl -G "http://${tidb-server-ip}:${tidb-server-status-port}/stats/dump/${db_name}/${table_name}" > ${table_name}_stats.json

• is the query always slow, or just occasionally

[jackysp]

Hi @gutongjiang ,
How about try to execute analyze table f_user;, then try the slow SQL again.

[gutongjiang]

@shenli

mysql> show create table f_entrust\G;
*************************** 1. row ***************************
Table: f_entrust
Create Table: CREATE TABLE f_entrust (
fid bigint(40) NOT NULL AUTO_INCREMENT,
fuid int(11) DEFAULT NULL,
ftradeid int(11) UNSIGNED DEFAULT NULL,
fbuycoinid int(11) UNSIGNED DEFAULT NULL,
fsellcoinid int(11) UNSIGNED DEFAULT NULL,
fstatus int(11) DEFAULT NULL,
ftype int(11) DEFAULT NULL,
fmatchtype int(11) DEFAULT NULL,
flast decimal(24,14) DEFAULT '0.00000000000000',
flastamount decimal(24,14) DEFAULT '0.00000000000000',
flastcount int(11) DEFAULT '0',
fprize decimal(24,14) DEFAULT NULL,
fcount decimal(24,14) DEFAULT NULL,
famount decimal(24,14) DEFAULT NULL,
fsuccessamount decimal(24,14) DEFAULT NULL,
fleftcount decimal(24,14) DEFAULT NULL,
ffees decimal(24,14) DEFAULT NULL,
fleftfees decimal(24,14) DEFAULT NULL,
fsource int(11) DEFAULT NULL,
fhuobientrustid bigint(11) DEFAULT NULL,
fhuobiaccountid int(11) DEFAULT NULL,
flastupdattime datetime(6) DEFAULT NULL,
fcreatetime datetime(6) DEFAULT NULL,
flivetime bigint(20) DEFAULT NULL,
fordertype int(1) DEFAULT '0' COMMENT '委单形態(市價 或限價)',
PRIMARY KEY (fid),
KEY fentrust_fuid (fuid),
KEY fentrust_fstatus (fstatus),
KEY fentrust_ftype (ftype),
KEY fentrust_fmatchtype (fmatchtype),
KEY fentrust_fsource (fsource),
KEY fentrust_fcreateTime (fcreatetime),
KEY fentrust_ftradeid (ftradeid),
KEY matchIndex (ftradeid,fstatus,ftype)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=5635853640 COMMENT='用戶委單列表'

• the query is always slow

[gutongjiang]

@jackysp the analyze doesn't work

[zz-jason]

@gutongjiang pay attention to this:

*************************** 4. row ***************************
id: └─TableScan_5
count: 144927.00
task: cop
operator info: table:f_user, range:[-inf,+inf], keep order:false

I think the full range table scan is the root cause of slow execution speed. The filter for this query is:

floginpassword = '3Eg+gKegvZ73HYz5c2c5JA==' and ftelephone = '13881964008'

Can you add the following index for this table, and testing the speed of the query again once the index is added:

alter table f_user add index idx_test(ftelephone, floginpassword);

-- or this index if the `password` is not suitable to be added in the index:
alter table f_user add index idx_test(ftelephone);

[shenli]

@gutongjiang Does this table has an index on floginpassword or ftelephone in MySQL?

[gutongjiang]

@shenli no

[shenli]

Could you post the explain result of MySQL? How many rows are there in MySQL?

[gutongjiang]

@shenli Sorry, the mysql server has shutdown, but I know there are about 144000 rows

[shenli]

@gutongjiang Have you set the time zone for your TiDB cluster? Or are you using the "System" timezone?

[gutongjiang]

@shenli the "System" timezone

[shenli]

@gutongjiang There is performance downgrade when using the System time zone. We are solving this issue. Please refer to this PR. We hope to merge it today.

In the current time, you can try to set the time zone explicitly (Use UTC or Asia/Shanghai). But I think TiDB could not be faster than MySQL in this size of data set.

[shenli]

@gutongjiang Have you tried setting the system timezone?
Or you could try the latest master.

[kolbe]

@gutongjiang we haven't heard back from you on this issue in more than 6 months, so I'm going to close this issue now. If you are still seeing this issue, please let us know!