The basic logic to use:
All things that are used only for the duration of a query are allocated in THD::mem_root through sql_alloc() or thd->alloc() except:
Things that may grow, like string buffers of type String. See sql/sql_string.cc.
Large blocks of memory used in one state of the query that can be released early. These are things like sort buffers, range trees, etc.
Things in libraries that are outside of MySQL's control (like hash tables).
Things that are needed a longer time should be alllocated with my_malloc() or through another MEMROOT.
Some objects have their own MEMROOT:
Copyright © 1997, 2014, Oracle and/or its affiliates. All rights reserved. Legal Notices