Expanded hints aren’t for everyday use. They force-enable (or disable) behaviors that typically work fine as-is, but in certain scenarios work better when explicitly controlled. For instance, an INDEX_MERGE hint tells the query optimizer which specific indexes to use—and no others—when performing index scans on multiple columns. This can speed up queries on a table with many indexes, or no composite index to cover a specific query. But don’t assume your ideas about how these optimizations work in theory will play out in practice. It’s always best to use an EXPLAIN statement along with a query optimization to find out if it does, in fact, speed things up.
Other new hints are useful for troubleshooting as well as performance. For instance, the MAX_EXECUTION_TIME() hint can abort a specific query after a certain number of milliseconds. This hint can be used to prevent “pathological” queries from tying up the system, but also as part of the process of debugging an existing query with a tendency to run wild.
A new XML type
Another forward-looking improvement, although it doesn’t actually do much yet, is a new XMLTYPE column type, introduced in MariaDB 12.3. Right now, all it does is accept a string of up to 4GB in size, and allow for selective replacement of XML with the UPDATEXML function. While there is no attempt to validate the XML or enforce a schema currently, those features are slated to land later, and to make XMLTYPE more functionally compatible with Oracle. It opens the path for future expansion.
