MySQL Forums
Forum List  »  Performance

Re: Query optimization needed
Posted by: Peter Brawley
Date: November 08, 2015 04:32PM

The latest Phorum upgrade here has introduced formatting bugs, one of which is that the reply window can move offscreen; another is that your first para shows as one long line, ie it fails to wrap. Did you type it in directly, or paste it in?

To benchmark & debug a query, turn caching off. I keep a sproc for it in a system DB ...

Create Procedure: setquerycaching( pcache bool )
BEGIN
  IF NOT pcache THEN
    IF @prev_query_cache_type IS NOT NULL THEN
      SET @prev_query_cache_type = @@LOCAL.query_cache_type;
    END IF;
    IF @prev_query_cache_size IS NULL THEN
      SET @prev_query_cache_size = @@GLOBAL.query_cache_size;
    END IF;
    SET LOCAL query_cache_type = 0;
    SET GLOBAL query_cache_size = 0;
    RESET QUERY CACHE;
  ELSE
    IF @prev_query_cache_type IS NOT NULL THEN
      SET LOCAL query_cache_type= @prev_query_cache_type;
    END IF;
    IF @prev_query_cache_size IS NOT NULL THEN
      SET GLOBAL query_cache_size= @prev_query_cache_size;
    END IF;
  END IF;
  SELECT
    pcache AS 'Query Caching',
    @@LOCAL.query_cache_type AS QCacheType, @prev_query_cache_type AS 'PrevType',
    @@GLOBAL.query_cache_size AS QCacheSize,@prev_query_cache_size AS 'PrevSize';
END;

qcache hits/inserts=0.28, the cache is likely helping only if the ratio > 0.5. For a roundhouse first pass at tweaking see "Optimisation cheatsheet" at http://www.artfulsoftware.com/infotree/mysqltips.php.

Indexes tend to improve Inner Join performance much more than Left Joins.

Options: ReplyQuote


Subject
Views
Written By
Posted
1728
November 05, 2015 01:42AM
662
November 05, 2015 11:40PM
757
November 06, 2015 11:25PM
Re: Query optimization needed
791
November 08, 2015 04:32PM
728
November 17, 2015 05:43AM
728
November 26, 2015 09:21PM


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.