Category: MySQL


Recently I faced a UNICODE encoding problem in MySQL Insert statement using PHP. When I tried to insert Swedish characters in MySQL table,the swedish characters ö, å, Ä were converted to ambiguous symbols because collation character set automatically converted to UTF8, usually I set MySQL collation into “utf8_general_ci”.

At last I find a solution in PHP to solve that problem, by using “mb_convert_encoding” we can convert encoding type at the insertion time.

$field = mb_convert_encoding($field,"iso-8859-1","utf-8");
	
@mysql_query("insert into table (field) values ('$field')");

This function will also work for other unicode character like spanish, Frecnch etc.

Advertisements

Import CSV to MySQL

Import from CSV to MySQL is pretty simple and just a matter of single line command. This post might be helpful for those are newbie in PHP/MySQL.

The command:


LOAD DATA LOCAL INFILE 'Drive Letter:\\\file_name.csv'
into TABLE table_name
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(field1,field2,field3)

Be careful about the “\\\” after drive letter and the data type for MySQL table fields. data type should be set according
to CSV data format.

MyISAM vs InnoDB in MySQL

MySQL has 10 Storage Engines. The storage-engine is what will store, handle, and retrieve information for a particular table. Most of us don’t bother about the Database Engine when creating the database. In Most cases, We use default storage Engine which is MyISAM.

Among the all Storage Engines MyISAM and InnoDb are the most popular storage Engines. These two storage engines perform in different ways in creating and  managing databases.

It will be better to identify which will best suited in which situation. Lets starts the discussion on this two:

MyISAM: MyISAM is the default storage engine. It is based on the older ISAM code but has many useful extensions. Data in MyISAM tables is split between three different files on the disk. One for the table format, another for the data, and lastly a third for the indexes.

  • Large files (up to 63-bit file length) are supported on file systems and operating systems that support large files.
  • There is a limit of 232 (~4.295E+09) rows in a MyISAM table.
  • The maximum number of indexes per MyISAM table is 64.
  • BLOB and TEXT columns can be indexed.

InnoDB: InnoDB is a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB row-level locking and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance.

The Comparison:

  1. InnoDB is more strict in data integrity while MyISAM is not.
  2. InnoDB implements row-level lock for inserting and updating while MyISAM implements table-level lock.
  3. InnoDB has transactions while MyISAM does not.
  4. InnoDB has foreign keys and relationship contraints while MyISAM does not.
  5. InnoDB has better crash recovery while MyISAM is poor at recovering data integrity at system crashes.
  6. MyISAM has full-text search index while InnoDB has not.

Decision Matrix

Is your table is going to be inserted, deleted, and updated much much more than it is going to be selected? InnoDB
If you need full-text search MyISAM
If you prefer/require relational database design InnoDB
Is disk-space or ram an issue? MyISAM
Not Sure? MyISAM

Advantages of InnoDB

  1. InnoDB should be consider first where data integrity comes as first priority because it supports relationship constraints and transactions.
  2. Faster in write-intensive (inserts, updates) tables because it utilizes row-level locking and only hold up changes to the same row that’s being inserted or updated.

Disadvantages of InnoDB

  1. More complex process for designing DB.
  2. Consumes more more memory space to perform tasks.
  3. Doesn’t support full-text indexing.

Advantages of MyISAM

  1. Easy to design and create.
  2. Faster than InnoDB on the whole as a result of the simpler structure thus much less costs of server resources.
  3. Supports Full-text indexing.
  4. Performs better in READ (SELECT) operations.

Disadvantages of MyISAM

  1. No data integrity (e.g. relationship constraints) check, which then comes a responsibility and overhead of the database administrators and application developers.
  2. Doesn’t support (ACID properties) transactions management which is important for secure data management.
  3. Slower than InnoDB for tables in the Write (INSERT, UPDATE ) operations.

Most of the time, I used PHP Date function to convert MySQL timestamp to Human readable format. But that’s really time consuming and need multiple lines of code. I had to fetch data from database as array/Object then convert that using PHP functions.Then I searched for a better solution and found MySQL FROM_UNIXTIME() function which is fantastic.

mysql> SELECT FROM_UNIXTIME(1196440219);
        -> '2007-11-30 10:30:19'
mysql> SELECT FROM_UNIXTIME(1196440219) + 0;
        -> 20071130103019.000000
mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),
    ->                      '%Y %D %M %h:%i:%s %x');
        -> '2007 30th November 10:30:59 2007'

Using FROM_UNIXTIME(), You can directly return timestamp value into human readable format in MySQL Record Sets.

Problems with ORDER BY RAND()

I was a frequent user of MySQL RAND() function like “SELECT id,title,publishDate FROM mytable ORDER BY RAND() LIMIT 5”. It was my first choice for random ordering the data in the MySQL tables. But at last, I got the idea that how painful it was.

MYSQL ORDER BY RAND () makes query slowest ever because MySQL first creates a temporary table with all the data, then orders it, and then applies the limit to the selection. That’s why even using LIMIT will not improve the performance. MYSQL ORDER BY RAND () operation assigns a random number ID to each row of the table and then delivers the results.

My previous thought about RAND () was to avoid that when there will be a large amount of data in the database but I was shocked when I run  the Apache benchmark utility (ab) for concurrent user testing and found that more than 85% request were failed for the use of ORDER BY RAND() even tables contains small amount of data like 2/3 rows.

So, It’s a good idea to avoid ORDER BY RAND() in MySQL because its performance is very poor and easily can destroy the scalability of your system whether it contain large or small amount of data  that doesn’t matter.