Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    Bucharest
    Posts
    37

    Post Unanswered: slow query (UNION) on a station with 4 processors and 8 gb ram

    Code:
    (
    SELECT SQL_CALC_FOUND_ROWS
    [...] - 12 rows
    FROM `produs`
    )
    UNION ALL
    (
    SELECT
    [...] - 12 rows
    FROM `produs_lgp` AS `produs`
    )
    ORDER BY `row3`
    LIMIT 0,25
    `produs` has - 30000+ rows
    `produs_lgp` has - 120000+ rows

    in the attached picture (screen_explain.gif) you will see an EXPLAIN statement result of the query above. can someone tell me from that explain if something is wrong? i don't understand why the query takes 20 seconds to execute (i've tried different my.cnf configurations and got nearly same results)

    [later edit]
    - i use UNION ALL for FOUND_ROWS
    - i use SELECT SQL_CALC_FOUND_ROWS because later i use SELECT FOUND_ROWS() for pagination, getting the number of total results. i also noticed that in the whole script (that is running this queries) the part with "SELECT FOUND_ROWS()" takes a lot of time..
    [/later edit]
    Attached Thumbnails Attached Thumbnails screen_explain.gif  
    Last edited by Zamolxe; 11-06-07 at 08:01. Reason: explanation about pagination

  2. #2
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    The 3rd line from the bottom. You are examining 112511 rows, and the analyser is not using a key.

Posting Permissions

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