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.
10 Older Entries »
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]
Sometimes we are not sure if the database design we have created
and our site has been using is correct and optimised. We all do
have this feeling at times don't we :)
Well, MySQL provides with PROCEDURE ANALYSE() to help us detect inconsistencies in our database design by suggesting for an optimal datatype and data length for columns.
The syntax for using PROCEDURE ANALYSE() is as below:
SELECT ... FROM ... WHERE ... PROCEDURE
We will create a sample database for our case here and …
10 Older Entries »