Today's Question:  What's your opinion about Alibaba mooncake incident?        GIVE A SHOUT

  CONTRIBUTE


Share your geeky stuff now.
You make the world better, the world makes us better.

  WRITE ARTICLE

 MYSQL


  MySQL index optimization

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 4000 The 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 * fr...

3,180 0 0          MYSQL INDEX SEARCH PARTITION


  How MySQL optmizes ORDER BY

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

1,520 0 0          MYSQL OPTIMIZATION INDEX ORDER BY


  Why should we keep column as NOT NULL instead of NULL in MySQL?

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 setting Many people think NOT NULL will require more space Many people don't want...

2,092 0 0          MYSQL REASON NULL


  How to optimize MySQL insert statement

For a big data system, one problem is the data access efficiency, one more problem is that the data insertion is very slow. We had a service system, the data loading process would take 4-5 hours. This time consuming operation is risky since if the program is interrupted during the loading process, it might be rerun, this will be troublesome. So it's necessary to improve the insertion efficiency for big data systems. Here we provide two optimization suggestions. 1. Combine multiple insert stateme...

5,382 0 0          MYSQL OPTIMIZATION INSERT


  How does MySQL handle DROP TABLE

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

2,203 1 0          MYSQL DROP TABLE


  Work with MySQL character set and collation

For non-English websites, they often have to deal with character set and collation if they want to store data to and read data from databases with other languages. Character set tells the database which kind of character encoding scheme to use to store or read data, collation can be simply understood as a subset of character set, it tells the database how to sort data. We talk about working with character set and collation of MySQL today.  In MySQL, if we want to store Chinese, Japanese or ...

8,907 0 3          MYSQL CHARACTER SET COLLATION CHINESE QUESTION MARK


  A serious security vulnerability found in MySQL/MariaDB

Recently a serious security vulnerability was found in MySQL/MariaDB. It relates to the access to the database. The issue is described below.When a user connects to MariaDB/MySQL, a token (SHA over a password and a random scramble string) is calculated and compared with the expected value. Because of incorrect casting, it might've happened that the token and the expected value were considered equal, even if the memcmp() returned a non-zero value. In this case MySQL/MariaDB would think that the p...

3,344 0 0          MYSQL BUG PASSWORD FIX MARIADB MEMCMP()


  Create Packed Indexes with MySQL

I am pretty much sure that you are aware of indexes which we are creating on database fields. Indexes are very useful to make your query search faster and improve the site performance. But the fact is that Indexes which we are creating on database tables also require some additional space on your hard disk and if you have not properly created these indexes than some t...

2,099 0 0          MYSQL COMPRESSED INDEX PACK_KEYS