MySQL Forums
Forum List  »  General

Re: Queries are very slow after replacing the client machine
Posted by: A J V PRASAD
Date: May 24, 2020 04:40AM

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!).

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.