Re: Another newbie question

[Peter Brawley]

To optimise, first turn off query caching so repeat benchmarks can be meaningful. We use an sproc for that ...

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

Then get a baseline benchmark, then run EXPLAIN EXTENDED on the query---that will tell you which elements need indexing help. Post the result here if the result puzzles you.

To see how a WHERE clause affects performance, comment it out and benchmark again.

AFAIK MySQL does not optimise Find_In_Set(), so it commits the engine to reading 60K rows, so it may be an argument for designing an intermediate (denormalised) result table with that result built in.