MySQL has had spatial indexes for many years, but they have all been Cartesian (X and Y coordinates) indexes. MySQL 8.0 adds support for geographic (latitude-longitude) indexes. In an earlier blog post, I described how the feature works. In this post, we’ll go into the details of how to upgrade from 5.7 to 8.0 if you have spatial indexes.…
10 Older Entries »
One of the big features of MySQL 8.0 is geography support. MySQL now has a catalog of spatial reference systems (SRSs), of which almost 500 are geographic. Most functions also support geographic computations. What about indexes?
MySQL 8.0 comes with InnoDB spatial indexes for geographic data.…
Notes/errata/updates for Chapter 6:
See the official book errata at http://tahaghoghi.com/LearningMySQL/errata.php – Chapter 6 includes pages 179 – 222.
At the end of the “Creating Tables” section (p.183-4), it says “We like using the underscore character to separate words, but that’s just a matter of style and taste; you could use underscores or dashes, or omit the word-separating formatting altogether.” While this is true, beware of using a dash, because MySQL will try to interpret “two-words”, thinking – is a minus sign. I recommend avoiding dashes for this reason (even though the book does this on page 215).
At the end of the “Collation and Character Sets” section (p.186), it says “When you’re creating a database, you can set the default character set and sort order for the database and its …[Read more]
We all know that if we add a MySQL index to speed up a read, we end up making writes slower. How often do we do the analysis to look at how much more work is done?
Recently, a developer came to me and wanted to add an index to a very large table (hundreds of gigabytes) to speed up a query. We did some testing on a moderately used server:
Set long_query_time to 0 and turn slow query logging on
Turn slow query logging off after 30 minutes.
Add the index (was on a single field)
Repeat the slow query logging for 30 minutes at a similar time frame (in our case, we did middle of the day usage on a Tuesday and Wednesday, when the database is heavily used).
Then I looked at the write analysis – there were no DELETEs, no UPDATEs that updated the indexed field, and no UPDATEs that used the indexed field in the filtering. There were only INSERTs, and with the help of pt-query-digest, here’s what I found: …[Read more]
In May, I presented two talks – one called “Are you getting the best out of your indexes?” and “Optimizing Queries Using EXPLAIN”. I now have slides and video for both of them.
The first talk about indexing should probably be titled “Why is MySQL doing this?!!?!!?” It gives insight into why the MySQL optimizer chooses indexes that you do not expect; especially when it does not use an index you expect it to.
The talk has something for everyone – for beginners it explains B-trees and how they work, and for the more seasoned DBA it explains concepts like average value group size, and how the optimizer uses those concepts applied to metadata to make decisions.
Slides are at http://technocation.org/files/doc/2017_05_MySQLindexes.pdf.
Click the slide image below to go to the video at …
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]
I wanted to share some of my first experiences and thoughts about this new feature.
Why is it good for us?
There are a couple of use cases. One of them is if you want to drop an index, but want to know the effect beforehand. You can make it invisible to the optimizer. It is a quick metadata change to make an index invisible. Once you are sure there is no performance degradation, you can then drop the index.
The main point is that the invisible index is unavailable for use by the optimizer, but it is still present and kept up-to-date by write operations. The optimizer won’t use it, even if we try to “FORCE INDEX”. I think we should be …[Read more]
In a previous post, I presented an Unexpected Memory Consumption for Bulk Index Creation in InnoDB. This was triggered by an increased innodb_sort_buffer_size and as stated in another post: "the sorting algorithm does not scale well with large sort buffers". In this post, I will present why it does not scale well and I will suggest solutions.
This post also answers feedback request for the
In my last Booking.com Hackathon, I worked on MyISAM vs InnoDB for data loading (LOAD DATA IN FILE) and bulk index creation. My motivation was the following: knowing that some are still using MyISAM for this particular use-case, I wanted to verify/understand if/why InnoDB is slower than MyISAM. I do not yet have complete results on this specific subject but I found some interesting things that
The slides for my Improving Performance With Better Indexes presentation at Percona Live 2015 MySQL Conference and Expo are now available.
In this presentation I discuss how to identify, review and analyze SQL statements in order to create better indexes for your queries. This includes understanding the EXPLAIN syntax and how to create and identify covering and partial column indexes.
This presentation is based on the work with a customer showing the 95% improvement of a key 15 table join query running 15,000 QPS in a 25 billion SQL statements per day …[Read more]
10 Older Entries »