Query Optimisation, Is there is any other way to change this query?

Databases
1

Query Optimisation, Is there is any other way to change this query. it takes some time to return result?

SELECT count(pagerno) as new
FROM tbldetails s1
WHERE s1.ccode = ‘$StrCode’
AND s1.col1 <> ‘invalid’
AND s1.col5 = ‘0’
AND s1.col6 <=‘$strDte’
AND s1.col6=(select MAX(s2.col6) FROM tbldetails s2
WHERE s1.pagerno = s2.pagerno AND s2.ccode = ‘$StrCode’
AND s2.col1 <> ‘invalid’ AND s1.col5 = ‘0’ AND s2.col6 <=‘$strDte’

2

Have you defined indexes on the columns you’re using here?

3

yes i did, following is the indexes columns.

Action Keyname Type Unique Packed Field Cardinality Collation Null Comment
t_idx BTREE No No detailid 38528 A YES
t_id BTREE No No ccode 265 A YES
col1 265 A YES
col6 9632 A YES
pagerno 38528 A YES
col5 38528 A YES