Showing entries 1 to 10 of 98
10 Older Entries »
Displaying posts with tag: optimization (reset)
Bad Optimizer Plan on Queries Combining WHERE, ORDER BY and LIMIT

Sometimes, the MySQL Optimizer chooses a wrong plan, and a query that should execute in less than 0.1 second ends-up running for 12 minutes !  This is not a new problem: bugs about this can be traced back to 2014, and a blog post on the subject dates of 2015.  But even if this is old news, because this problem recently came to my attention, it is a problem worth writing on.

This is an improved

The Low Hanging Fruits of MySQL – Percona Live Community 2022

MySQL is one of the top used database system and with highly active community. Though without proper configuration you may not squeeze the most out of it.This will be a talk about the low hanging fruits to get the best outcome preferably in a short time or with small efforts. This will include configuration, performance and the most common problem scenarios that you can relate, examine and implement. I got opportunity to talk at Percona Live Community 2022. Please find the recording and slides below.

The post The Low Hanging Fruits of MySQL – Percona Live Community 2022 first appeared on ..::CHANGE is INEVITABLE::...

MySQL JDBC Statement Caching

Introduction In this article, we are going to see how we can enable the MySQL JDBC Driver Statement Caching mechanism, which, unfortunately, is not enabled by default. No matter what data access framework you are using, you still need to configure the JDBC Driver if you want to get the most out of the MySQL database engine. MySQL JDBC client-side PreparedStatement As I explained in this article, by default, the MySQL JDBC Driver emulates prepared statements. So, no matter if you are executing a plain Statement or a PreparedStatement, the SQL statement... Read More

The post MySQL JDBC Statement Caching appeared first on Vlad Mihalcea.

Constant-Folding Optimization in MySQL 8.0

In MySQL 8.0.16 the optimizer has improved again! Comparisons of columns of numeric types with constant values are checked and folded or removed for invalid or out-of-rage values. The goal is to speed up query execution.

MySQL Functional Indexes

Since MySQL 5.7 one can put indexes on expressions, aka functional indexes, using generated columns. Basically you first need to use the generated column to define the functional expression, then indexed this column.

Quite useful when dealing with JSON functions, you can find an example here and the documentation there.

Starting with MySQL 8.0.13 we have now an easiest way to create functional indexes (or functional key parts as mentioned in the documentation) \o/

Let’s see how with a quick practical example.

Using FORCE INDEX: When Your Query Optimizer Gets It Wrong

A Guest Post from Jordan Raine, Clio

Jordan Raine is a staff software developer at Clio, the leading provider of legal practice management, client relationship management, and client intake software. Clio is dedicated to helping lawyers meet client needs and run efficient, profitable practices. Jordan wrote this post to help mySQL developers save time/effort and ultimately improve their customers' experience. 

Unlike most code a developer writes, writing SQL only requires us to describe what data we want and not how to get it. When given a query like SELECT id, author_id FROM posts WHERE author_id = 123 ORDER BY id, you needn’t concern yourself with what indexes are used (if any), what type of sort is used, or any other number of implementation details. Instead, the query optimizer handles this for you. This keeps SQL concise and …

[Read more]
How to Select the Right Queries to Optimize

Narrowing down which queries to optimize is a step in database administration that is often skipped - however it shouldn't be!  

Why is selecting the right queries to optimize so important? There are several reasons. 

Penny-Wise, Pound-Foolish  Look at the forest, not just the trees, and optimize globally, not locally. If you’re optimizing a query that never causes a user-visible problem, doesn’t impact other queries, and doesn’t significantly load servers, you might be “optimizing” things that don’t matter, spending more money than you save. Your time has value, too! Keep in mind, too, that optimizing a query that generates only 1% of the database’s overall load will not be a significant benefit to the bottom line.   Whack-A-Mole Queries It’s very common to  find a slow query in a log file, try re-executing it and  then find that it is fast. …

[Read more]
Webinar Wednesday, January 24, 2018: Differences between MariaDB and MySQL

Join Percona’s Chief Evangelist, Colin Charles as he presents Differences Between MariaDB and MySQL on Wednesday, January 24, 2018, at 7:00 am PST (UTC -8) / 10:00 am EST (UTC -5).

Register Now

Tags: MariaDB, MySQL, Percona Server for MySQL, DBA, SysAdmin, DevOps
Experience Level: Novice

MariaDB and MySQL. Are they syntactically similar? Where do these two query languages differ? Why would I use one over the other?

MariaDB is on the path of gradually diverging from MySQL. One obvious example is the internal data …

[Read more]
Webinar January 18, 2018: MySQL Troubleshooting and Performance Optimization with Percona Monitoring and Management (PMM) Part 2

Join Percona’s Product Manager Michael Coburn as he presents MySQL Troubleshooting and Performance Optimization with Percona Monitoring and Management (PMM) Part 2 on Thursday, January 18, 2018, at 11:00 am PST / 2:00 pm EST (UTC-8).

Register Now

Tags: Percona Monitoring and Management, PMM, Monitoring, MySQL, Performance, Optimization, DBA, SysAdmin, DevOps
Experience Level: Expert

Optimizing MySQL performance and troubleshooting MySQL problems are two of the most critical and challenging tasks for …

[Read more]
InnoDB Performance Optimization: Webinar Q & A

Thank you for attending my webinar on Wednesday, December 20, 2017, InnoDB Performance Optimization. In this blog, I will provide answers to the Q & A for the webinar.

Are the T2 CPUs similar to the M4 series?

I would expect them to be similar. Amazon does not disclose what specific version of CPUs they use for T2 instances. More details are available here.

Delay in spinlock code is pretty old code. Need to optimize based on today’s CPU? Your views?

There have been a number of improvements to the InnoDB Spinlock code during the last few years. For example, using CPU …

[Read more]
Showing entries 1 to 10 of 98
10 Older Entries »