Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2005
    Posts
    2

    Unanswered: SQL statement for Mysql and Mssql

    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

  2. #2
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    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'
        )
    )

  3. #3
    Join Date
    Nov 2005
    Posts
    2

    mysql slow

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •