Very High Activity
I Use This!


Analyzed 16 days ago. based on code collected 16 days ago.
Posted 9 months ago by Ike Walker
A while ago I blogged about a potential workaround for MySQL error 1070 that could be used to add a unique constraint with more than 16 columns. As a reminder here's the error you get when you try to create a unique constraint with more than 16 ... [More] columns in MySQL: ERROR 1070 (42000): Too many key parts specified; max 16 parts allowed The solution I proposed should work, but when I started to implement it I made a couple of changes that merit this follow-up post. One change is fairly generic, and the other is specific to my use case. The generic change was to only concatenate some of the columns, rather than all of them. Specifically I only need to concatenate N-15 columns. So if I want to have a unique constraint on 20 columns, I can include 15 of those actual columns and then concatenate the remaining 5 columns into a new column that is included as the 16th column in the unique constraint. When choosing which columns to concatenate, it makes sense to choose columns with short values, since that limits the amount of redundant data you are storing and helps prevent the table from taking up too much disk space. As in my previous post, here's a table with 20 columns: CREATE TABLE IF NOT EXISTS lots_of_columns ( c1 int not null, c2 int not null, c3 int not null, c4 int not null, c5 int not null, c6 int not null, c7 int not null, c8 int not null, c9 int not null, c10 int not null, c11 char(8) not null, c12 char(8) not null, c13 char(8) not null, c14 char(8) not null, c15 char(8) not null, c16 char(8) not null, c17 char(8) not null, c18 char(8) not null, c19 char(8) not null, c20 char(8) not null ) ENGINE=InnoDB DEFAULT CHARSET=utf8; I can include 15 of those columns in my unique constraint, and concatenate the other 5 together to make the 16th column in the index. Looking at the schema, it's not immediately obvious which columns are the smallest. The INT columns can have values ranging from 1 to 11 characters long, while the CHAR columns can have values ranging from 1 to 8 characters long. Since the range of lengths is fairly small I could choose the 5 columns to concatenate arbitrarily, or if I have a representative data set I could run some queries using AVG(CHAR_LENGTH())) to empircally determine which columns are shortest. For the sake of simplicity in this example I'll use columns c16 - c20. Now I add a new column to store those 5 columns concatenated together. The column needs to be long enough to store the concatenated values. Since each CHAR column value can be up to 8 characters long, and the hyphens will account for 4 characters, the new column needs to store up to (5 * 8) + 4 = 44 characters. Here's the DDL to add the new column and the unique constraint: ``` alter table lots_of_columns add column c16_thru_c20_concatenated varchar(44) not null default '', add unique index unique_constraint (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16_thru_c20_concatenated); ``` If the table has data in it I can populate the new concatenated column for all existing rows with an update statement: update lots_of_columns set c16_thru_c20_concatenated = concat(COALESCE(c16,''),'-', COALESCE(c17,''),'-', COALESCE(c18,''),'-', COALESCE(c19,''),'-', COALESCE(c20,'')) where c16_thru_c20_concatenated = ''; The update will fail if any existing data violates the new unique constraint. Now that the existing data is updated, I need to make sure the concatenated column is populated properly going forward. In my previous post I used triggers to populate the concatenated column. In some ways that is the safest implementation, because it practically guarantees that the concatenated column will always be set regardless of how the table is written, but it adds some overhead to inserts and updates. In my specific use case I am writing tens of thousands of rows at a time to the table using LOAD DATA INFILE. Once written the rows are never updated, and there are no other code paths writing to the table, so for me it makes more sense to set the values in the LOAD DATA INFILE statement rather than using triggers. Here's an example to load the contents of a tab-delimited text file into my database table: ``` load data infile '/tmp/lots_of_columns.txt' ignore into table lots_of_columns (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,@c16,@c17,@c18,@c19,@c20) set c16=@c16, c17=@c17, c18=@c18, c19=@c19, c20=@c20, c16_thru_c20_concatenated=concat(COALESCE(@c16,''),'-', COALESCE(@c17,''),'-', COALESCE(@c18,''),'-', COALESCE(@c19,''),'-', COALESCE(@c20,'') ) ``` An alternative implementation could be to use virtual columns, as pointed out by some of the commenters on my previous post. I have not tried that approach yet -- mostly because I don't use MariaDB or MySQL 5.7 -- but I may set up a sandbox instance to try that out soon. [Less]
Posted 9 months ago by Chris Calender
MySQL 5.6.22 was recently released (it is the latest MySQL 5.6, is GA), and is available for download here. For this release, there is 1 “Security Note”, 2 “Functionality Changed”, and 5 “Compilation Notes”, all benign, but let me address them: ... [More] Security Note: The linked OpenSSL library for the MySQL Commercial Server has been updated from version 1.0.1h to version 1.0.1j. Issues fixed in the new version are described at Functionality Changed: Replication: The variable binlogging_impossible_mode has been renamed binlog_error_action. binlogging_impossible_mode is now deprecated. (Bug #19507567) Functionality Changed: Security: yaSSL was upgraded to version 2.3.5. (Bug #19695101) Compilation Notes: These are all rather minor, so I’ll spare the full entries here. However, if you build MySQL from source, it would be worth several minutes to read the 5 notes in the changelogs. In addition to those, there were 46 other bug fixes: 17 InnoDB   9 Replication 19 Miscellaneous   1 Partitioning The highlights for me are 6 of the InnoDB bugs, as 2 were regression bugs (crashing/corruption), 2 potentially corruption causing, another crashing, and 1 halting: InnoDB: An ALTER TABLE operation raised an assertion. When a foreign key object was removed from the dictionary cache, an incorrect foreign key object was removed from the rb-tree. (Bug #19908343) References: This bug is a regression of Bug #18806829. InnoDB: Pages with a checksum value of zero were incorrectly treated as empty pages. A page should only be considered empty if its checksum value and LSN field values are zero. (Bug #19500258, Bug #73689) References: This bug is a regression of Bug #17335427. InnoDB: The dict_set_corrupted() function attempted to update the clustered index of the SYS_INDEXES data dictionary table incorrectly. (Bug #19584379). InnoDB: The InnoDB data dictionary was not updated when a ALTER TABLE … CHANGE COLUMN operation changed the case of the column name. (Bug #19465984). InnoDB: A memory access violation caused fts_optimize_thread and mysqld to terminate. (Bug #19314480). InnoDB: A procedure, called from a function to perform an operation on a temporary table, caused the server to halt/stall. (Bug #19306524) Conclusions: So while there were no major changes, those 6 InnoDB bugs (2 being regression bugs) are definitely of concern, so I’d be sure to review these to see if you’re running an affected version, and consider upgrading if so. And with the yaSSL updates, if you use SSL connections, you may want to consider upgrading as well. The full 5.6.22 changelogs can be viewed here (which has more details about all of the bugs listed above): Hope this helps.   [Less]
Posted 9 months ago by Chris Calender
MariaDB 5.5.41 was recently released (it is the latest MariaDB 5.5), and is available for download here: This is a maintenance release, and so there were not too many changes, *but* please take notice as ... [More] there are 2 very important bug fixes: Bug Fixed: A fix to a serious bug in InnoDB and XtraDB that sometimes could cause a hard lock up of the server (Bug #MDEV-7026) Bug Fixed: A fix to unnecessary waits in InnoDB and XtraDB (Bug #MDEV-7100) Includes all bugfixes and updates from MySQL 5.5.41 (MySQL 5.5.41 Overview and Highlights) TokuDB updated to version 7.5.3 Update: We now offer openSUSE repos, see the repository configuration tool for details on how to use it. Thus if you’re running pre-5.5.41 and have encountered, or even if you may be prone to, either bug #7026 or #7100, you should plan to upgrade. If interested, the official MariaDB 5.5.41 release notes are here: And the full list of fixed bugs and changes in MariaDB 5.5.41 can be found here: Hope this helps.   [Less]
Posted 9 months ago by Michael "Monty" Widenus
2014 was a productive year for the MariaDB Foundation.Here is a list of some of the things MariaDB Foundation employees haveaccomplished during 2014:The 3 full-time MariaDB Foundation developers have worked hard to make MariaDB better:Some 260 ... [More] commitsSome 25 reviews of code from the MariaDB community.Fixed some 170 bugs and new features. For a full list, please check Jira.Reported some 160 bugs.Some of the main new features Foundation developers have worked on in 2014 are:Porting and improving MariaDB on IBM Power8.Porting Galera to MariaDB 10.1 as a standard feature.Query timeouts (MDEV-4427)Some coding and reviews of Parallel replication in MariaDB 10.1.Working with code from Google and Eperi to get table space and table level encryption for InnoDB and XtraDB.Allowing storage engines to shortcut group by queries (for ScaleDB) (MDEV-6080).Moronga storage engine (reviews and porting help)Connect storage engine (reviews and porting help)Spider storage engine (merging code with MariaDB)Query timeouts (MDEV-4427)Merge INET6_ATON() and INET6_NTOA() from MySQL-5.6 (MDEV-4051)Make "CAST(time_expr AS DATETIME)" compatible...SQL Standard) (MDEV-5372)Command line variable to choose MariaDB-5.3 vs MySQL-5.6 temporal data formats (MDEV-5528)Added syntax CREATE OR REPLACE to tables, databases, stored procedures, UDF:s and Views (MDEV-5491. The original TABLE code was done by Monty, other parts was done as a Google Summer Of Code project by Sriram Patil with Alexander Barkov as a mentor.Upgraded the bundled Perl Compatible Regular Expression library (PCRE) to 8.34 (MDEV-5304)Reduced usage of LOCK_open (MDEV-5403) (MDEV-5492) (MDEV-5587)Ported patches from WebScaleSQL to MariaDB (MDEV-6039)Better preallocation of memory (MDEV-7004)Lock-free hash for table definition cache (MDEV-7324)A lot of speed optimizations (changing mutex usage, better memory allocations, optimized bottlenecks, memory barriers etc).The MariaDB documentation/knowledgebase:has now 3685 articles about MariaDB and MySQL. Foundation employees added during 2014 223 new ones and did 6045 edits.Some of the main new articles from us are:All the system and status variables for all storage engines and plugins should be documented, including variable differences between MariaDB 5.5 versus MariaDB 10.0 and also MariaDB 10.0 versus MySQL 5.6.Updated documentation to changes related to MariaDB 10.1Upgrading from MariaDB 5.5 to MariaDB 10.0SpiderOQGRAPHGaleraSphinxMroongaInformation Schema TablesCommon MariaDB QueriesC APImysql database tablesOverview of MariaDB logsOLD_MODEEncryption of tables and table spaces in MariaDB 10.1Some 10 blog posts (This we need to do better..)We also have a lot of outside contributors and translators. Thanks a lot to all of you!We also visited and talked about MariaDB at a lot of conferences:February: Community events in Japan & Korea.April: The first MariaDB Foundation conference. This was a free for all event and we made videos of all presentations!April: Talk and booth at Percona live in Santa Clara.April: Talks at Linux Fest BellinghamJuly: Booth and BoF at Oscon PortlandOctober: Talk at All your Base at Oxford.October Talk about MySQL and MariaDB for China entrepreneurs in Beijing as part of China Finland Golden Bridge.November: Talk at Codemesh in London.November: Talks at PHP Buenos AiresNovember: Talk about open source business models at Build stuff" in Vilnius.November: Keynote and talk at CodeMotion Milan.In addition I had several talks at different companies who were moving big installations to MariaDB and needed advice.We where also able to finalize the MariaDB trademark agreement between the MariaDB corporation and the MariaDB Foundation. This ensures that that anyone can be part of MariaDB development on equal terms. The actual trademark agreement can be found here.On the personnel side, we were sad to see Simon Phipps leave the position as CEO of the Foundation.One the plus side, we just had 2 new persons join the MariaDB foundation this week:We are happy to have Otto Kekäläinen join us as the new CEO for the MariaDB foundation! Otto has in the past done a great work to get MariaDB into Debian and I am looking forward to his work on improving everything we do in the MariaDB foundation.Vicențiu Ciorbaru has joined the MariaDB foundation as a developer. In the past Vicențiu added ROLES to MariaDB, as part of a Google Summer of Code project and he is now interested to start working on the MariaDB optimizer. A special thanks to Jean-Paul Smets at Nexedi for sponsoring his work at the foundation!Last, I want to give my thanks to the MariaDB foundation members who made all the foundation work possible for 2014:AutomatticMariaDB corporation (former SkySQL Ab)ParallelsZeinmaxFor 2015 we welcome a new member, Visma. Visma will be part of the foundation board and will help push MariaDB development forwards.As the above shows, the MariaDB Foundation is not only a guarantee that MariaDB will always be an actively developed open source project, we also do a lot of development and practical work. This is however only possible if we have active members who sponsor our work!If you are interested in helping us, either as a member, sponsor, or by giving development resources to the MariaDB foundation, please email us at foundation at ! [Less]
Posted 9 months ago by Chris Calender
MySQL 5.5.41 was recently released (it is the latest MySQL 5.5, is GA), and is available for download here: This release, similar to the last 5.5 release, is mostly uneventful. There was only 1 ... [More] “Functionality Added or Changed” bugs this time, and 14 bugs overall fixed. Out of the 14 bugs, there were 6 InnoDB bugs, and 2 replication bugs, all of which seemed rather minor or obscure. The one worth noting is the “Functionality Added or Changed” item, which was: yaSSL was upgraded to version 2.3.5. (Bug #19695101) With the recent yaSSL issues, if you use SSL certificates, you may want to consider upgrading to ensure you’re using the latest yaSSL. Lastly, I should note there were some CMake notes, so if you compile MySQL yourself and use CMAKE, please see the full 5.5.41 changelogs. For reference, the full 5.5.41 changelog can be viewed here: Hope this helps.   [Less]
Posted 9 months ago by MariaDB
Tue, 2015-01-13 10:59vilho_raatikka_lApplications are often built on top of single MySQL-compliant database instance but often there is a need for more performance and/or availability than what one database instance can provide. Adding slaves or ... [More] replacing standalone database server with full-fledged MySQL-compliant cluster often requires changes to the application. MaxScale and its Read Write Split router (rwsplit, for short) attempts to hide the complexity brought by multiple backend servers and to minimize the need for application changes by taking care of authentication, managing connections and providing session management on behalf of the client. Read Write Split router is a plug-in module, which can be used with MaxScale to receive, examine and forward queries sent by clients. Routing decision is based on query type and some other criteria, such as transaction state, and replication lag. For rwsplit, every cluster consists of a master and slaves. In case of Galera or NDB cluster, the monitor plugin for the particular database environment elects one of the nodes to act as the master node, which rwsplit treats as a master where it routes writes and transactional queries. The session data can be considered as the state of the client connection, this includes any SQL variables that have been set, the current database and transaction state. Any command that might alter this state is considered to be a session command. Examples of such commands are 'SET @myvar:=5', 'SET AUTOCOMMIT=false', or 'USE test'. Queries after session data modification must see the new data regardless of which backend server rwsplit routes them. MaxScale's rwsplit router allows for queries to be routed to master or any of the slaves used by the session. Therefore subsequent queries may be executed in different servers, which makes it necessary for an rwsplit router to send session data modifications to all backend servers. It detects when an incoming query includes session update and multiplies the command to all session's backends. Since the client waits for a single reply to the query, and multiple servers send multiple replies to MaxScale, only the first is routed to the client while redundant ones are discarded. On the contrary, queries which read session variable values, for example, SELECT * from mysql.user where host=@myhost, can be routed to any available backend server. Direct client/server connection   Client connection to MySQL Replication cluster through Read/Write Split router   Session modification commands and execution order In a single thread execution mode handling session variable is easy: detect, route, receive and filter out all but one response which is then sent to the client. MaxScale, however, is a multi-threaded, non-blocking server where threads are not dedicated to specific client or task. Moreover, rwsplit router executes session variable writes in an optimistic way, which means that first response to session variable update is sent to client - which may then continue sending queries to MaxScale. And of course, responses from backend servers may arrive anytime, in any order, and in multiple pieces. Therefore, it is possible that one of the slaves updates session variable, say @myvar:=5, faster than other backends and its response packet is sent to the client, which immediately tries to read @myvar. Since session variable read can be executed in any backend, rwsplit router may send the read to a slave which haven't even received the variable write yet. In order to maintain the execution order between session updates and subsequent queries, rwsplit suspends the execution of further queries until the backend server has executed all session commands that were initiated before the query. Adding a backend database node to session When client connects to rwsplit router service, master and slaves are selected for the session. If master fails during session, session is aborted, but if slave fails it can be replaced with another slave or the failed node can rejoin the session. Search for replacement is triggered by monitor as soon as the failure is detected. A node selection routine is called, and necessary amount of slaves searched and connected. New backend candidate nodes must have all session modifications executed before they can join. That is, new node candidates must catch up with other members first. Rwsplit saves all session modification commands to session's command history. Each session member is provided with a cursor to the beginning of the command history list and executing the listed commands is part of the process of becoming a session member. As a result, every backend node in the session shares same session data with other session nodes, which makes it possible to balance reads and writes between the cluster nodes. Limitations The known limitations are partially due to the nature of variables in MySQL and partially due to limitations in rwsplit implementation. Since session management relies on statement replication (from MaxScale to backend servers), they are executed in multiple backends under different conditions (location, time, etc.). Thus, the result of execution of a session modification command may differ in different nodes. If the new value depends on environment or time, the result typically differs in each of the nodes. Example of such a command is : SELECT @mytime:=now() . Another known limitation is that if session modification command is embedded into write statement, it won't be replicated to slaves. Rwsplit detects the query type correctly but extraction and replication of partial query is not implemented yet. An example of an embedded session modification command : INSERT INTO test.t1 values (@myvar:=5) . Current implementation is based on optimistic method, which assumes that a session modification command either fails or succeeds in all backend nodes. Thus, cases, where the first reply (routed to the client) contains different result than the responses from other backends include a risk of invalid behavior. Imagine a case where 'USE test' succeeds in one of the typically slowest slaves but fails in other, more stressed backends - and especially in the master. The client assumes that it is using 'test' instead of 'product' and executes set of writes, which are routed to master where administrator just dropped the test database before the execution of 'USE test'. Thus, optimism and asynchronous replication can potentially cause unwanted damage. If session modification command routing fails to any of the backends due to backend failure, for example, client session will be closed. This behavior is limited to session modification command routing only. If backend failure is noticed by monitor or in normal SQL query routing, query must be re-executed but session remains untouched. Summary In addition to all other its features, MaxScale's Read/Write Split router provides transparent client session management, which, despite its current limitations, makes it possible to use an application originally designed for single database server, with a MySQL-compatible database cluster without modifications. Rwsplit handles the query routing and response transfer in an optimistic way, which every time works as fast as fastest backend server. The current method includes known, yet small risks, but the implementation is also likely to evolve as we get more experiences from the users and from testing. Tags: ClusteringHigh AvailabilityLoad balancingMaxScaleProxy About the Author Vilho Raatikka Vilho is a Senior Software Engineer working mainly on MaxScale. Vilho has worked with databases server technologies since the year 2000 for IBM, Solid Information Technology and Helsinki University as software engineer, teaching assistant and researcher. [Less]
Posted 9 months ago by Severalnines
January 12, 2015 By Severalnines MySQL 5.6 has an extensive list of new features and changes, so upgrading from a previous version can be risky if not tested extensively. For ... [More] this reason, we recommend our users to read and understand the changes before doing the upgrade. If you are on older MySQL versions, it is probably time to think about upgrading. MySQL 5.6 was released in February 2013, that’s almost two years ago! A major upgrade, e.g., from MySQL 5.5 to 5.6 or MariaDB 5.5 to 10, requires the former MySQL/MariaDB server related packages to be uninstalled. In Galera Cluster, there are two ways to upgrade; either by performing offline upgrade (safer, simpler, requires service downtime) or online upgrade (more complex, no downtime).    In this blog post, we are going to show you how to perform an offline upgrade on Galera-based MySQL/MariaDB servers, from MySQL 5.5.x to 5.6 or MariaDB 5.5 to 10.x with Galera 3.x, on Redhat and Debian-based systems. The online upgrade procedure will be covered in a separate post. Prior to the upgrade, determine the database vendor and operating system that is running at ClusterControl > Settings > General Settings > Version: Note that different database vendor and operating system combinations use different installation steps, package names, versions and dependencies.    Offline Upgrade in Galera   Offline upgrade is recommended if you can afford scheduled downtime. The steps are straightforward and the probability for failure is significantly lower. Performing an online upgrade gives you availability at the cost of operational simplicity.   read more [Less]
Posted 9 months ago by MariaDB
Fri, 2015-01-09 13:19maria-luisaraviolMariaDB MaxScale is now RC and together with all the MariaDB team that has been involved in the project we need to thank all the companies that agreed to become part of the MaxScale Beta Test Plan. This major ... [More] step in the MaxScale life (read more here) had an important impact on the MaxScale QA process. We have asked some companies to help us in testing MaxScale in “real” environments with different custom settings, different configurations and with traffic load as close to reality as possible. Colt Engine, an Italian hosting company based in Turin (Italy) applied immediately. They have been a great fan of MariaDB MaxScale and have been enthusiastic early testers of MaxScale since early 2014 when it still was Alpha. Actually they have suggested some of the MaxScale filter plugins. I started drafting a possible testing environment with them in November. In order to provide the best possible test environment they have been brave and taken the risk to test MaxScale in production on one of their non-critical projects. The technical details of the ultimate test environment and the results of the testing activity will be described in future blog posts later this month, what I want to show in this post is some of the infinite possible ways in which MaxScale can help DBAs and Architects to solve several problems and easily design a flexible architecture. Colt Engine is an ISP, and also the official Italian host provider for the Joomla! users they represent, in my opinion, one of the best kind of testers. As an example, in Joomla! hosting environments, in most (if not all) of the cases they do not have a clue on what applications are running on their servers and how they are accessing the MariaDB or MySQL instances (they are now supporting both MariaDB and MySQL, but gradually moving all the servers to MariaDB). It’s not easy to forecast traffic loads or the number of connections a specific application will need at a certain point in time and how to manage and distribute them. Some of the Joomla! plugins that are available, use deprecated statements or statements that have been altered (“TYPE =” versus “ENGINE=” in the CREATE TABLE statement, just to mention the most popular one). In this particular case, MaxScale can filter all the deprecated statements and gives Colt Engine the flexibility to decide whether to send them to a specific server with a consistent MariaDB/MySQL release or, even better, replace the deprecated keyword (as in the example above) with the most updated syntax and keep all the servers aligned with the same version of database server. It’s easy to understand that if you have to look after hundreds of servers, a consistent, replicated hence scalable and homogeneous database environment is definitely the way to go, However when this is not possible or during times of transition, MaxScale can give the flexibility to integrate different database releases and consistently route queries to specific servers according to specific rules. Another part of the test environment involved a further important MaxScale filter: the Tee filter. Actually as we will see in next post in this blog series, their specific usage of the Tee filter gave important feedback to our MaxScale engineers that, thanks to that, have been happy to refine the original Tee filter design and allow a more sophisticated usage of it. Want to learn more about MariaDB MaxScale and how it can help your organization? Please visit MariaDB MaxScale Product Page. Tags: MaxScale [Less]
Posted 9 months ago by Ted Wennmark
Covering Indexes not being chosen by optimizerI noticed this problem about a year ago when writing this blogpost. In short problem is when adding a covering index and keeping old non-covering index the optimizer opted to use old non-covering index. ... [More] Only solution was to FORCE optimizer to use covering index which meant you needed to modify your DML or remove old index.Using the same test setup as in my old blogpost but when you add new covering index do not drop the old index.So, instead of running:mysql> ALTER TABLE big DROP INDEX CountryCode;mysql> ALTER TABLE big ADD INDEX conPop (CountryCode, Population);We run only statement for adding new covering index and do not remove old index:mysql> ALTER TABLE big ADD INDEX conPop (CountryCode, Population);With MySQL 5.6 you will see the following output from EXPLAIN:mysql> EXPLAIN SELECT CountryCode, SUM(Population) from big group by CountryCode\G           id: 1  select_type: SIMPLE        table: big         type: indexpossible_keys: CountryCode,conPop          key: CountryCode         <-------- Wrong index      key_len: 3          ref: NULL         rows: 259729        Extra: NULLProblem is that optimizer is not using our new covering index even though it would be the fastest way to access data. Only solution is to drop the old index or add keyword FORCE INDEX to your SQL.With latest version of MySQL (MySQL 5.7.5) there is a small release note here stating: "If the optimizer chose to perform an index scan, in some cases it could choose a noncovering rather than a covering index. (Bug #18035906)".Lets see output from EXPLAIN using the same scenario as described above with MySQL 5.7.5:mysql> EXPLAIN SELECT CountryCode, SUM(Population) from big group by CountryCode\G           id: 1  select_type: SIMPLE        table: big   partitions: NULL         type: indexpossible_keys: CountryCode,conPop          key: conPop             <--------- Covering index      key_len: 7          ref: NULL         rows: 259729     filtered: 100.00        Extra: Using index1 row in set, 1 warning (0,00 sec)Great news, with MySQL 5.7.5 the optimizer is now picking the covering index and query is 3x quicker!! [Less]
Posted 9 months ago by Sean Hull
Join 29,000 others and follow Sean Hull on twitter @hullsean. Oh RDS, you offer such promise, but damn it if the devil isn’t always buried in the details. Diving into a recent project, I’ve been looking at upgrading RDS MySQL. Major MySQL upgrades can be a bit messy. Since the entire engine is rebuilt, queries […]