Cassandra is an open source distributed database, it combines dynamic key/value and column oriented feature of Bigtable.Features of Cassandra are:Flexible schema, no need to design schema first, it's very convenient to add or delete stringsSupport range search on keysHigh usability, extensible. The single node error will not affect the cluster.We can think Cassandra's data model as a 4 or 5 dimensional Hash.COLUMNColumns is the smallest data unit in Cassandra, it is a 3 dimensional data type including name,value and timestamp. { // This is a column name: "逖靖寒的�...

Yesterday, my friend Liu Bing asked me a question about how to select top 3 values for each person in a database table using SQL. I think this question is interesting and it deserves some thoughts. Here I record down how to solve this issue.Assume we have a table which has two columns, one column contains the names of some people and the other column contains some values related to each person. One person can have more than one value. Each value has a numeric type. The question is we want to select the top 3 values for each person from the table. If one person has less than 3 values, we select...

If you want to debug the issue of "MySQL server has gone away", you can reproduce it with below steps:Modify configuration file:sudo vi /etc/mysql/my.cnf  Make below changes:[mysqld]  wait_timeout = 30  interactive_timeout = 30  Restart the service:sudo /etc/init.d/mysql restart  Write below PHP codes:$link = mysql_connect('127.0.0.1', 'root', 'root');  if (!$link) {      die('Could not connect: ' . mysql_error());  ...

Database access speed is always the bottle neck of many applications. Many application have large amount of data to search, retrieve and display nowadays. How do we improve the performance of our applications, how do we reduce the cost of database access? Apart from the design of database, the quality of the query is also one important factor to take care.  But before that, we need to know how much network traffic a query will consume.Yunyang,Zhang from Nubee in Singapore did some research about MySQL query cost in his post "How Much Network Traffic Does A MySQL Query Consume?". Let's hav...

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

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

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

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