Today's Question:  What does your personal desk look like?        GIVE A SHOUT

 ALL


  Why (offset, limit) is slow in database select?

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...

2,734 0       MYSQL OFFSET LIMIT SLOW


  Guide on recovering data in MySQL

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...

3,392 0       BINLOG EXAMPLE DATABASE MYSQL BACKUP


  How to reset root password in MySQL 8

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...

59,997 5       MYSQL PASSWORD MYSQL 8


  Fix 'this authentication plugin is not supported' issue while using Go to connect MySQL 8

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...

14,512 4       GO MYSQL 8 AUTHENTICATION PLUGIN MYSQL


  Unknown system variable 'query_cache_size' in DbVisualizer while accessing MySQL

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 ...

11,878 0       MYSQL 8 MYSQL DBVISUALIZER QUERY_CACHE_SIZE


  Workaround size limit of phpMyAdmin import sql file

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...

11,853 0       MYSQL SOLUTION PHPMYSQLADMIN 80M IMPORT LIMIT


  PHP Apache MySQL Set-up Note

With the emergence of WAMP, LAMP, PHP developers are liberated from the tedious work of setting up PHP environment. Since PHP, Apache and MySQL are so tightly bundled, WAMP and LAMP provide a setp solution for setting up a PHP environment which includes the programming programming environment, server and database. But for a PHP who wants to learn more, you have to try to set the PHP environment yourself by installing PHP, Apache and MySQL manually and configuring them.Below is a simple note on how to setup the PHP environment on Mac OS X. This setup guide should also be helpful on other system...

4,475 0       PHP APACHE MYSQL


  <=> operator in MySQL

Have you ever seen "<=>" in a SQL query while using MySQL? Does it mean less and equals to and greater than? Actually if you consider it as the union of <= and =>, great, you are close to it. This is one form of equal operator in MySQL, it has the similar meaning to the = operator with some subtle difference.According to MySQL documentation, <=> is NULL-safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.For example:mysql> SELECT 1 <=> 1,...

3,897 2       MYSQL NULL SAFE <=>