Maybe you’re wondering why in MySQL/MariaDB 'string' seems to be the same as 'STRING'. Or maybe that’s not the case for you, but you would like to make a case insensitive search. This article explains how to write a case ...
10 Older Entries »
Optimal Indexing for Performance – How to plan Index Ops. ?
An index or database index is a data structure which is used to quickly locate and access the data in a database table. Indexes are created on columns which will be the Search key that contains a copy of the primary key or candidate key of the table. These values are stored in sorted order so that the corresponding data can be accessed quickly (Note that the data may or may not be stored in sorted order). They are also Data Reference Pointers holding the address of the disk block where that particular key value can be found. Indexing in database systems is similar to what we see in books. There are complex design trade-offs involving lookup performance, index size, and index-update performance. Many index designs exhibit logarithmic (O(log(N))) lookup performance and in some applications it is possible to achieve flat (O(1)) performance. Indices can …[Read more]
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.
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.…
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]
10 Older Entries »