In production environment databases, it is often seen that some SQL where conditions include:equal condition on a normal index + primary key range query + order by limitAlthough using a normal index would be more efficient, the system chooses to use index merge instead in some cases. This article explores such index merge situations.Index Merge Official Introduction  The Index Merge access method retrieves rows with multiple range scans and merges their results into one.Generally, for a single table, the optimizer chooses one index. However, in the case of index merge, the optimizer ...

When the CPU usage of a MySQL database rises abnormally, it's necessary to quickly identify the problematic SQL statements. In this post, we try to provide one way to achieve this. Below are the actual steps.1. Use the top command to find the threads with the highest CPU usage in the MySQL process.# Find the MySQL process IDps -ef | grep mysql# Use the process ID to find the thread IDs with the highest CPU usage:top -H -p In top, press P to sort by CPU usage.Note the thread ID, for example, 39449.2. Log in to the database to query performance_schema and information_schema.-- Query the thr...

Starting from a problemFive years ago when I was working at Tencent, I found that MySQL request speed was very slow in the pagination scenario. With only 100,000 data, a select query on a single machine took about 2-3 seconds. I asked my mentor why, and he asked in return, "In an indexing scenario, what is the time complexity to get the nth largest number in MySQL?"The pursuit of the answerConfirming the scenarioAssuming there is an index on the "status" column, a query like "select * from table where status = xx limit 10 offset 10000" will be very slow. Even with a small amount of data, there...

In our daily work, there might be mistakes made which got some data or even databases deleted in MySQL. If this happens on production, it would be a nightmare. In case this happens, normally DBA would jump in to save the world. And they would try to recover the data from the backup if there is any. But if there is no backup, then the show stops.Hence database backup is necessary on production environments to avoid such awkward situation. Also normally in MySQL, binlog should be enabled as well in ROW mode so that recovery can be replayed starting from the backup time point to the incident time...

The user password in MySQL is stored in the user table, the password reset is actually to change the value of record in this table. To change the password in case the password is forgotten, the idea is to bypass the authentication of MySQL and get into the system and update the record password value with SQL command.In MySQL 5, one can start MySQL service with --skip-grant-tables option, this option will tell the service to skip loading the grant tables when starting, hence the root user can login with empty password.mysqld –skip-grant-tables After login, can run below SQL command t...

MySQL 8 has changed its default authentication plugin from mysql_native_password to caching_sha2_password to improve its security. However many third party libraries seem act slowly to catch up with this change. This causes some compatible issues with their connection to MySQL. One of the issues is seen in Go libraries while it's trying to connect to MySQL 8.The specific error has been observed is "this authentication plugin is not supported". The root cause of this issue is that the go-sql-driver didn't support the new default authentication plugin. This bug has been reported a coup...

DbVisualizer is a very popular GUI application to view the data stored on a database. It supports various databases such as Oracle, MySQL etc. It relies on different database drivers to connect to the database server and access the data stored there. In this post, we will try to solve one problem when accessing the latest MySQL database(8.0+) through DbVisualizer. MySQL database starting from 8.0 has lots of changes which may not be compatible with old version of software or libraries which interact with it. There are also lots of issues reported while accessing the 8.0+ version of MySQL ...

When doing website development with MySQL, we often need to do database backup and restore. For website, the data in database will grow quickly, so when we back up the database, the size of the generated sql file may be over 80MB which is the max allowed size when we want to import a sql file for restoring our database using phpMyAdmin. To workaround this limit, we need to review the documentation of phpMyAdmin. Fortunately, I found an online article written by David Pratt  which gave us a very simple solution to this problem.Find the config.inc.php file located in the phpmyadmin director...