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

Some simple introduction to the SQLite function interfaces. First let's check some error codes defined in SQLite3 (They are in SQLite3.h file in the SQLite installation).#define SQLITE_OK           0   /* Successful result */  /* beginning-of-error-codes */  #define SQLITE_ERROR        1   /* SQL error or missing database */  #define SQLITE_INTERNAL     2   /* Internal logic error in SQLite */  #define SQLITE_PERM    &nbs...

ClickHouse is a high-performance open-source columnar database management system developed by Yandex. Here are some of the key features of ClickHouse:Columnar storage: ClickHouse uses a columnar storage format, which allows it to efficiently store and retrieve data by column, rather than by row. This results in much faster query performance, especially for analytical and aggregate queries.Real-time data processing: ClickHouse is designed to handle real-time data processing and can handle billions of rows of data with sub-second query times.Massively scalable: ClickHouse can scale to handle mas...

After installing MySQL, when we try to start MySQL, sometimes we may not be able to start it. The reasons can be different. We share some general cases where MySQL cannot be started.Case 1: Directory or file permission issueIf the permission is set wrongly in MySQL's $datadir and its sub directories or files, MySQL will not be able to read and write files normally.Error message:mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data/usr/local/mysql/bin/mysqld_safe: line 107: /usr/local/mysql/data/imysql.local.err: Permission deniedCase 2: Port conflictThere may be other my...

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

MongoDB is an open source document oriented database, developed with C++, it is to solve some real problems existing among development communities. In October 2007, MongoDB development began at 10gen, in 2009, MongoDB was open sourced as a standalone product. 10gen provides technical support, training and consultancy service.According to 10gen education, 10gen will provide some training courses for MongoDB starting from 22 October, 2012. Now it's available for registration.There are two courses currently :M101 and M102, target to developers abd DBAs. The courses will end on 10 December 2012, 1...

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