Re: Utilize maximum system resources for better performance

[Rick James]

> now my queries takes 1.xx seconds

Turn the QC back on while we fix those queries.
Let's see one of them. Please show us these 3 things

SELECT...
EXPLAIN SELECT ...
SHOW CREATE TABLE

We may be able to speed up the query so that it will be fast even if the QC is off. This will _indirectly_ help the UPDATEs.

> 2-3 table joins

The number of tables being JOINed is not as critical as the indexes involved. Let's see and example; include the 3 things mentioned above.

> hex chars falls unders ascii so we can set ascii character set. and if the field contains non ascii characters then we need to use utf which uses more bytes per char.

Correct. (I guess I left out a few steps in the logic.)
Also, CHAR preallocates the full size; VARCHAR is flexible. In your `hash` case you had both CHAR and utf8.

> it needs to change queries in my whole app.

Sorry, if there were a magic wand, I would have given you that. Instead the fix is more invasive. Maybe others readers can fix their apps before it becomes such a big task.

Oh, since you already have the slowlog turned on, you could use pt-query-digest to summarize it, then show us the first couple of queries (together with the 3 things above). Those _may_ be quick wins in the performance arena.