Re: Query optimization needed

[Peter Brawley]

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.