Re: Queries are very slow after replacing the client machine
At last, I am at work place. To ease the troubleshooting I have created old client setup ( scenario_1 ) and new client setup ( scenario_2 ) in my laptop ( with a hope to exclude hardware related issues).
Scenario 1:
# more /etc/redhat-release
Red Hat Enterprise Linux Server release 5.9 (Tikanga)
# uname -a
Linux localhost.localdomain 2.6.18-348.el5 #1 SMP Wed Nov 28 21:22:00 EST 2012 x86_64 x86_64 x86_64 GNU/Linux
# mysql -V
mysql Ver 14.12 Distrib 5.0.95, for redhat-linux-gnu (x86_64) using readline 5.1
# time mysql_run.sh < t1.sql > t1.txt
real 0m2.802s
user 0m0.020s
sys 0m0.017s
# wc t1.txt
73 11678 64197 t1.txt
#### query explain result
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 6
ref: const,const
rows: 27974
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: c
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 29
ref: const,func
rows: 1
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: d
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 13
ref: maindb.a.branch,maindb.a.agency_code
rows: 1
Extra:
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: e
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 13
ref: maindb.a.branch,maindb.a.agency_code
rows: 1
Extra:
*************************** 5. row ***************************
id: 1
select_type: SIMPLE
table: b
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 12
ref: maindb.a.branch,maindb.a.policy,const
rows: 1
Extra: Using where
--------------- End Scenario 1 ---------------------
Scenario 2
# more /etc/redhat-release
Red Hat Enterprise Linux Server release 7.4 (Maipo)
# uname -a
Linux localhost.localdomain 3.10.0-693.el7.x86_64 #1 SMP Thu Jul 6 19:56:57 EDT 2017 x86_64 x86_64 x86_64 GNU/Linux
# mysql -V
mysql Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64) using readline 5.1
# time mysql_run.sh < t1.sql > t1.txt
real 12m33.622s
user 0m0.013s
sys 0m0.014s
# wc t1.txt
73 11678 64197 t1.txt
### explain result in grid view
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 6
ref: const,const
rows: 27974
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: c
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: const
rows: 953376
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: d
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 13
ref: maindb.a.branch,maindb.a.agency_code
rows: 1
Extra:
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: e
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 13
ref: maindb.a.branch,maindb.a.agency_code
rows: 1
Extra:
*************************** 5. row ***************************
id: 1
select_type: SIMPLE
table: b
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 12
ref: maindb.a.branch,maindb.a.policy,const
rows: 1
Extra: Using where
----------------- End scenario 2 ---------------------
diff of explain scenario 1 scanario 2
16c16
< type: eq_ref
---
> type: ref
19,21c19,21
< key_len: 29
< ref: const,func
< rows: 1
---
> key_len: 2
> ref: const
> rows: 953376
----- END diff of explain -----------------
--- The troubling part of the query ( t1.sql )
left join
table_c c
on
(
c.ben_reference = cast(a.policy as char(9) )
and
ben_type = 'PH'
)
--- END the troubling part of the query ------
Note on the troubling query part
1. left side of first comparison is char and indexed ; right side is cast from decimal (indexed) to char. After casting only, the query run faster in scenario 1.
I understand execution plan by new client ( scenario 2) is the issue.
And I wonder execution plan is dependent on client!
Thanks for the troubleshooting advice. Waiting for suggestions.
(Now I wish to test without cast, but it will take one more day!).