SQL statement for Mysql and Mssql

darylteh · #1 (**permalink**) 11-25-05, 01:26

hi all, i have a query where i need 4-5 min to execute in Mysql while i need only 2-3 sec in MSSQL...please have a look!

select pricezone.* FROM pricezone WHERE (pricezone.PZ_PriceZoneID in (SELECT customerzone.cz_pricezoneid FROM customerzone,outletsalesman WHERE(customerzone.cz_outletcode = outletsalesman.os_outletcode AND outletsalesman.os_salesmancode = '?') OR customerzone.cz_outletcode = '0'))

Thank you!undefined

jfulton · #2 (**permalink**) 11-25-05, 10:05

What version of MySQL are you running? What is your table structure?

Try running this and share the results:

Code:

EXPLAIN 
select pricezone.* 
FROM pricezone 
WHERE (pricezone.PZ_PriceZoneID in 
    (
    SELECT customerzone.cz_pricezoneid 
    FROM customerzone,outletsalesman 
    WHERE 
    (customerzone.cz_outletcode = outletsalesman.os_outletcode AND outletsalesman.os_salesmancode = '?') 
    OR customerzone.cz_outletcode = '0'
    )
)

darylteh · #3 (**permalink**) 11-29-05, 03:16

sorry for the late reply! I'm using mysql ver5.0. I just try to get the information from different table. Below is the result that i got

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, 'PRIMARY', 'pricezone', 'ALL', '', '', '', '', 2027, 'Using where'
2, 'DEPENDENT SUBQUERY', 'customerzone', 'ALL', '', '', '', '', 755, 'Using where'
2, 'DEPENDENT SUBQUERY', 'outletsalesman', 'index', '', 'PRIMARY', '58', '', 4591, 'Using where; Using index'

I'm new in MYSQL and the performance isquite disappoint me..
what do you think? and thanks for your reply!

Internet Services

Web Development

Digital Marketing

Consumer Tech