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
10 Older Entries »
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.
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.
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
SELECT id, author_id FROM
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 …
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]
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).
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]
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).
Tags: Percona Monitoring and Management, PMM,
Monitoring, MySQL, Performance, Optimization, DBA, SysAdmin,
Experience Level: Expert
Optimizing MySQL performance and troubleshooting MySQL problems are two of the most critical and challenging tasks for …[Read more]
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]
But the real magic happens on the backend. This is the ecosystem that really powers your website. One writer has articulated this point very nicely as follows:
The technology and programming that “power” a site—what your end user doesn’t see but what makes the site run—is called the back end. Consisting of the server, the database, and the server-side applications, it’s the behind-the-scenes functionality—the brain of a site. …[Read more]
Some days ago a customer arrived with the following table:
CREATE TABLE `infamous_table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `member_id` int(11) NOT NULL DEFAULT '0', `email` varchar(200) NOT NULL DEFAULT '', `msg_type` varchar(255) NOT NULL DEFAULT '', `t2send` int(11) NOT NULL DEFAULT '0', `flag` char(1) NOT NULL DEFAULT '', `sent` varchar(100) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `f` (`flag`), KEY `email` (`email`), KEY `msg_type` (`msg_type`(5)), KEY `t_msg` (`t2send`,`msg_type`(5)) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
And a query that looked like this:
SELECT COUNT(*) FROM `infamous_table` WHERE `t2send` > 1234 …[Read more]
10 Older Entries »