In-depth MYSQL Optimizations

1. We all have systems where several SQL queries are repeatedly triggered. Everytime the same query is fired, it will prepare a optimization plan, fetch from inno_db buffer pool (if the page is cached in memory) and will re-run the joins, etc causing lots of CPU cycles.

MYSQL (upto 5.7) has the inherent capability to cache repetitive queries, resulting in a lot less overhead.

To enable caching in MYSQL, we used following variables & values:

query_cache_type = 1
query_cache_size = 64MB

1. We all know that when it comes to text searching MySQL is not the right choice. But sometimes there is no other alternative but to use LIKE query which essentially results in a full table scan. However, there is a brilliant alternative:

MySQL provides inverted-indexes just like Apache Lucene and calls it FULL-TEXT indexes that we can leverage and get upto 10–100X performance improvements.

For example: below is a typical LIKE query:

But when we change the query and use match("") against("")along with FULL-TEXT indexes on relevant columns, it resulted in a 50X gain in performance.

References:

1. https://dev.mysql.com/doc/refman/5.7/en/query-cache.html
2. https://dev.mysql.com/doc/refman/5.7/en/fulltext-natural-language.html