Join Percona’s Senior Technical Services Engineer, Adamo Tonete as he presents MongoDB Index Types: How, When and Where Should They Be Used? on Wednesday, July 12, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7). Register Now MongoDB has 12 index types. Do you know how each works, or when you should use each […]
Read More
As part of our support services, we do a lot of query optimization. This is where most performance gains come from. Here’s an example of the work we do. Some days ago a customer arrived with the following table:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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:
|
1 2 3 4 |
SELECT COUNT(*) FROM `infamous_table` WHERE `t2send` > 1234 AND `msg_type` LIKE 'prefix%'; |
The table had an index t_msg that wasn’t […]
Read More
MySQL 8.0 has a new feature called “invisible indexes,” which allow you to quickly enable/disable indexes from being used by the MySQL Optimizer. 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 […]
Read MoreThis post will discuss some excellent methods of getting JSON to MySQL indexing to work smoothly. What is JSON JSON is an text based, human readable format for transmitting data between systems, for serializing objects and for storing document store data for documents that have different attributes/schema for each document. Popular document store databases use […]
Read MoreMySQL meets NoSQL with JSON UDF I recently got back from FOSDEM, in Brussels, Belgium. While I was there I got to see a great talk by Sveta Smirnova, about her MySQL 5.7 Labs release JSON UDF functions. It is important to note that while the UDF come in a 5.7 release it is absolutely […]
Read MoreHere at the MySQL Performance Blog, we’ve been discussing the several new features that MySQL 5.6 brought: GTID-based replication, InnoDB Fulltext, Memcached integration, a more complete performance schema, online DDL and several other InnoDB and query optimizer improvements. However, I plan to focus on a series of posts on the small but handy improvements – […]
Read More