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 statementCommon insert statement use is:INSERT INTO `insert_table` (`datetime`, `uid`,&nb...

There are times where running IF statements inside a query can be useful. MySQL provides a simple way to do this through the use of IF and CASE statements.The IF statement takes three arguments; the conditional, the true value and the false value. False and true values may be static values or column values. For example:SELECT IF(score > 100, 100, score) AS scoreFROM exam_resultsthis will return the value in the score column limited to a maximum value of 100. IF statements can also be nested:SELECT IF(score > 100, 100, IF(score < 0, 0, score))FROM exam_resultsCASE statements (s...

Sometimes if we want to run MySQL but we don't have phpMyAdmin installed. What should we do?On Windows we can run MySQL in following steps:Open Console Window->Type "mysqld -u root". This means we start the mysql server with username root. This program will handle all the communications between our console window and the actual data;After starting the server, we need to use a client to connect to the server and communicate with it. the mysql program is right for this. Next in the command console, we type "mysql -u root -p" to connect the server. By default, after the installation,the defaul...

By default remote access to MySQL database server is disabled for security reasons. However, some time you need to provide remote access to database server from home or a web server. If you want to remotely access to the database server from the web server or home, follow this quick tutorial.MySQL Remote AccessYou need type the following commands which will allow remote connections.Step # 1: Login Using SSH (if server is outside your data center)First, login over ssh to remote MySQL database server:ssh user@mysql.nixcraft.iStep # 2: Edit my.cnf FileOnce connected you need to edit the MySQL ser...

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 times your index size become more than your actual data size.Now what if you can compress indexes which you have ...

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 password is correct, even while it is not.  Because the protocol uses random strings, the probab...

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

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