Problem description:We need to access a very big table which has 40 million records. In the table, id is the primary key and program_id is indexed.When executing a select query:select * from program_access_log where program_id between 1 and 4000The above query executes very slowly, we thought it was because there were too many records in the table. So we added id in the where condition to limit the search so that each time only half a million records would be read.select * from program_access_log where id between 1 and 500000 and program_id between 1 and 4000But this query is st...

A few days ago, when executing DROP TABLE in MySQL, all processes including DDL and DML were hung until DROP TABLE was completed. I am confused about this phenomenon.I have reviewed the source codes of MySQL to check how MySQL internally handle DROP TABLE.When user trigger DROP TABLE command:########################MySQL SERVER drop table########################/* Sql_table.cc  delete (drop) tables. */bool mysql_rm_table( )  /*   Execute the drop of a normal or temporary table */  int mysql_rm_table_part2 ()    /* Remove matching tables from the HANDLER&...

Keep table column as NOT NULL instead of NULL except in some special cases. This statement is cited by many articles of MySQL optimization, but they don't say why we should do this. Here we discuss about this.First why are there many people using NULL when defining table? The reasons may be:NULL the the default when defining table, the rookies or people who don't want to have much troubles will keep this default settingMany people think NOT NULL will require more spaceMany people don't want to verify the data before inserting or updating, it's more convenient when writing SQL queries.There are...

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

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

For both small free database space and large e-commerce websites, reasonable database table structure design is essential. To achieve this, it requires us to have a full understanding of commonly used data types in database system. Below we share some knowledge about data types in MySQL.1. Numeric typesThe numeric types can be classified as : integer, float and decimal type.The so-called "decimal" refers DECIMAL and NUMERIC, they are of the same type. Strictly speaking it is not a numeric type, because they are actually stored as strings; Every single digit of its value(including the decimal p...

The MySQL family has been innovating rapidly. New features need names and sometimes those names are confusing. Describing something as multi-master or a NoSQL solution has confused me.Multi-master requires one of conflict prevention, conflict resolution or faith. MySQL Cluster provides both conflict prevention and resolution as described in these great posts. Regular MySQL has minimal support for conflict prevention (auto-increment-offset can prevent insert conflicts) and thus requires faith that the application does the right thing. Regular MySQL gets conflict pr...

In some situations, MySQL will just use an index to fulfill the requirement of an ORDER BY or GROUP BY statement without extra sorting.Although ORDER BY will not have the exact match with index, index can still be used as long as the portion that is not included in the index is included in the where clause.The following queries will all use index to process the ORDER BY or GROUP BY part:SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2;SELECT * FROM t1 WHERE key_part1=constant GROUP BY key_part2;SELECT * FROM t1 ORDER BY key_part1 D...