Archive for the ‘MySQL’ Category

How to log all MySQL queries in Drupal

In order to benchmark a Drupal site performance you need to see all the database queries related to your Drupal site. In case you don’t have access to the my.cnf file If you don’t have access to the my.cnf file, you can log the queries from the Drupal code itself: cp includes/ includes/ vim includes/ […]

Insert into multiple MySQL tables with one-to-one relationship

I have been asked if there is a way to insert data into two MyISAM tables that connected by a one-to-one relationship in one MySQL query. For example: mysql> CREATE TABLE `user` ( `id` int(10) unsigned NOT NULL auto_increment, `name` varchar(255) NOT NULL default ”, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1   mysql> […]

Connection Pool: MySQL Communications link failure

The Problem And The Solution While using a MySQL connection pool in Java, I received a MySQL Communications link failure Exception (see below). In order to solve communication link failure exception: I have removed JDBC property autoReconnect=true and put only the JDBC property autoReconnectForPools=true I have added the connection properties: testOnBorrow testWhileIdle timeBetweenEvictionRunsMillis minEvictableIdleTimeMillis See […]

Connection Pool: MySQL connection closed exception

The Problem While using a MySQL connection pool in Java, I received a MySQL Connection Closed Exception (see below). The problem was that the JDBC driver was not compatible to the MySQL server version. I have used version 5.05a for both JDBC and MySQL and it solved the problem The problem is that the connection […]

How to set up a MySQL connection pool in Java

MySQL connection pool A MySQL connection pool is a pool of connections to MySQL database. Opening and maintaining a database connection for each process (or thread), is time costly (connection creation time) and wastes resources (connections). Connection pool increase the performance of (Java) applications that needs to connect to the database by reusing the connections. […]

How to rename a database in MySQL

In MySQL there is no support for database renaming. In order to rename a MySQL database you can do one of the following: 1. Create new database and rename all tables in the old database to be in the new database: CREATE database new_db_name; RENAME TABLE db_name.table1 TO new_db_name, db_name.table2 TO new_db_name; DROP database db_name; […]

Don’t Alter Table. Do Copy and Rename

In some cases a MySQL MyISAM table structure needs to be alter. This includes adding, removing and changing table columns (or indexes) and even re-ordering the MySQL table. In these cases, for performance and safety reasons, it is wise to avoid altering the current working MySQL table and adopt the Copy and Rename approach. The […]

How to select random rows in MySQL

The easiest way to generate random rows in MySQL is to use the ORDER BY RAND() clause. SELECT col1 FROM tbl ORDER BY RAND() LIMIT 10; This can work fine for small tables. However, for big table, it will have a serious performance problem as in order to generate the list of random rows, MySQL […]

How to determine the leading prefix part of a MySQL index

In MySQL, an index can be limited to use only a leading prefix part of a column values. Prefixes can be specified for CHAR, VARCHAR, BINARY, and VARBINARY columns, while for BLOB and TEXT columns the prefix must be given. For example, the statement shown here creates an index using the first 20 characters of […]

MySQL select count

COUNT function counts the number of rows retrieved by a SELECT statement. The return value is of type BIGINT. The COUNT(expr) function count the number of non-NULL values of expr in the rows retrieved by a SELECT statement. SELECT COUNT(expr) FROM tbl; In contrast, COUNT(*) is different in that it returns a count of the […]