MySQL recently fixed a known bug (I was not aware of this before) of OPTIMIZE TABLE (in MySQL Server 5.1.55, 5.5.9) in which OPTIMIZE TABLE statement was not preserving the AUTO_INCREMENT counter on OPTIMIZE TABLE operation.

Want to see yourself? Lets do it (code taken from the MySQL bug report)

CREATE TABLE a (a INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT) ENGINE=INNODB;
 
INSERT INTO a VALUES ();
INSERT INTO a VALUES ();
INSERT INTO a VALUES ();
 
DELETE FROM a ;
 
SHOW TABLE STATUS LIKE 'a';

In the below table status, you can see the AUTO_INCREMENT value is 4 (even after deleting all the rows which is normal behavior).

mysql> SHOW TABLE STATUS LIKE 'a'\G
*************************** 1. row ***************************
           Name: a
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 3
 Avg_row_length: 5461
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 4194304
 Auto_increment: 4
    Create_time: 2011-02-20 07:52:18
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

Now execute the OPTIMIZE TABLE command and check the table status

OPTIMIZE TABLE a;
 
SHOW TABLE STATUS LIKE 'a';

mysql> SHOW TABLE STATUS LIKE 'a'\G
*************************** 1. row ***************************
           Name: a
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 4194304
 Auto_increment: 1
    Create_time: 2011-02-20 07:52:18
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

MySQL fixed this bug 5 years after the bug report.

So be careful with OPTIMIZE TABLE if you have older MySQL versions.

TRUNCATE TABLE reset the AUTO_INCREMENT to 1 so don't confuse this example with TRUNCATE TABLE.

MySQL does not provide any native function to count the number of times a specific word exists in a column or string. But you can do it yourself by using MySQL string functions LENGTH() and REPLACE(). e.g. you have a column description and want to count number of instances of a word 'iPhone'. This query will return the number of occurrences of word iPhone.

SELECT (LENGTH(description) - LENGTH(REPLACE(description,'iPhone','')))  / LENGTH('iPhone')
FROM table

I just attended MySQL webinar "What's New: MySQL 5.5 and MySQL Enterprise Edition". You may find the recording of this webinar at mysql.com. It was nice to see improved InnoDB performance (10x) as compared to MySQL 5.1 InnoDB. A couple of people in the webinar reported that they have experienced the enhanced performance with MySQL 5.5.

Here are some of the questions that looked interesting to me

Q: you have comparion charts between myisam and innodb?
A: We've just completed our MyISAM and InnoDB benchmarking and will be posting a whitepaper this week.

Q: Can you compare MySQL 5.5 partitioning schemas and performance with MS SQL Server 2008?
A: I'd recommend the following whitepaper for details: http://www.mysql.com/why-mysql/white-papers/mysql_wp_partitioning.php

Q: Are there any linux graphs/stats with using 2 or 4 core CPU's? The previous linux graphs were with 6 cores.
A: Here is a link to additional details regarding some of the benchmarks. Many of them start w/ 4 cores enabled and then expand to additional cores. As 2 cores scaled well with previous version, I believe most benchmarks started with 4 cores: http://assets.en.oreilly.com/1/event/36/What_s New in MySQL 5_5__ Performance and Scalability Benchmarks Presentation.pdf

Q: The white paper referenced for partitions is for 5.1. I am interested in the features and extensions that have been implemented in 5.5 A. Is there a newer white paper? Thanks
Q: Yes -- Here are improvements for 5.5 regarding partitioning http://dev.mysql.com/tech-resources/articles/mysql_55_partitioning.html

Q: Does MySQL support sub-partitioning ?
A: Yes http://dev.mysql.com/doc/refman/5.5/en/partitioning-subpartitions.html

Q: Is there any testing data available that focuses on the improved innodb performance in a virtual/cloud environment rather then the larger multi-core physical testing platforms noted in the presentation
A: I'd recommend reviewing the detailed presentation posted from our users conf: http://www.mysql.com/news-and-events/on-demand-webinars/display-od-532.html

Today I found a couple of useful articles about upgrading MySQL 4. One is written by Peter Zaitsev and other by Guillaume Lefranc.

They are talking about problems you may face while upgrading as well as benefits of MySQL 5.

I hope you guys can really benefit from it.

Red Hat Enterprise Linux 5 and CentOS 5 are now shipping MySQL 5.0.77 instead of MySQL 5.0.45 on security grounds.

This is moderate security release according to RHEL and CentOS.

MySQL.com is conducting webinars on its usage in online applications. It is a series of 5 webinars i.e.

Part 1: Introduction to MySQL Scale Out and Replication Services
In the first installment we look at the basics for deploying MySQL for a scalable online application such as:

• Choosing the right architecture
• Scale Out - What, Why and How?
• Using MySQL Replication Services
• MySQL Solutions to help manage your high-growth business

North America -- Tuesday, August 18, 2009: 10:00 Pacific time / 1:00 Eastern time

EMEA -- Wednesday, August 26, 2009: 10:00 am European Central Time

It is a common requirement to count number of words in a column of a MySQL database table. But MySQL does not have any built in function to count number of words as it usually has for number of purposes.

But you don't need to worry about it. It can be achieved with a simple trick e.g. you have a column name and want to count number of words in it. This query will return number of words separated with space in a column

SELECT SUM( LENGTH(name) - LENGTH(REPLACE(name, ' ', ''))+1)
FROM table

If you want to count number of words separated by some other string for example comma (,) or semicolon (;), simply replace space with your required character.

I am creating a list of MySQL GUI (front end) tools. I hope you will contribute this list.

A wide variety of MySQL GUI tools are available in the market. Most of them are free but some are paid as well. We can divide GUIs in 2 categories i.e. desktop and web based applications. Here is the list of popular MySQL GUI tools I have heard of

1. Desktop Application
   • MySQL Workbench
   • Old official MySQL GUI Tools (Linux, Windows, Mac OSX)
   • HeidiSQL
   • dbForge Studio (requires .NET Framework 2.0, 3.0 or 3.5)
   • Navicat (Linux, Windows, Mac OS X)
   • SQLyog
   • CocoaMySQL (Mac OS X)
   • SQL-Front
   • Toad for MySQL
   • ACDB sql client - Supports MySQL and Oracle
   • Aqua Data Studio (Linux, Windows, Mac OS X, Solaris)
   • DreamCoder (Windows)
   • DBACentral (Windows)
   • SQL Manager (http://www.sqlmanager.net/de/products/mysql/manager)
   • DatabaseSpy
2. Web Based Application
   • phpMyAdmin
   • MySQL Quick Admin
   • MyWebSQL

If you know any good GUI Tool, please let me know.

People have different opinions on saving images in database. Some says, "Why bother database if we can handle this by saving images to disk". I am agree with this;).

Most of the requirements can be fulfilled by saving the images to disk. This reduces the unnecessary load on MySQL.

Here is the small code to save the image in MySQL with the help of PHP.
Read the rest of this entry »

This is a very simple example to download data in csv format using PHP and MySQL. I have used a table with two columns i.e. id and name.

The main point to download file is a proper use of header(). It has said many times in many places but I repeat here, "Make sure there must not be any sort of output (including whitespaces) before header()". We can also set a default file name for the file.

Here is the PHP code
Read the rest of this entry »