Programming Tips & Insights SQL / MySQL Tips and Tutorials

When to use NoSQL (e.g. MongoDB) and when to use SQL (e.g. MySQL)?

NoSQL is mostly about document databases while SQL is about relational databases.

  1. NoSQL vs. SQL
  2. Documents vs. Tables
  3. MongoDB vs. MySQL
  4. Property vs. Entity

The trick is 4: Property vs. Entity

Design your data models on a piece of paper.

Use MongoDB if …

If you see more trivial *entities* such as category, tag, brand, origin, etc. that are actually properties, go with NoSQL databases such as MongoDB because these are properties of documents. What you are trying to store is some kind of documents of these properties.

Use MySQL if …

If you see more real entities such as business, person, product, event, etc., go with a relational database such as MySQL because these are entities that should get tables on their own. What you are trying to store is a network of related entities.


Go with the solution that mandates the LEAST tables or document types so the structure is as simple as possible.

If you find the data structure becoming increasingly complex and difficult to maintain, you are probably using the wrong database technology.

SQL / MySQL Tips and Tutorials

Optimize for Large Number of Columns in a Wide MySQL Table

In addition to necessary indexes and proper SELECT queries (only select needed columns), for wide tables, 2 basic tips apply well:

  1. Define your column as small as possible.
  2. Avoid using dynamic columns such as VARCHAR or TEXT as much as possible when you have large number of columns per table. Try using fixed length columns such as CHAR. This is to trade off disk storage for performance.

For instance, for columns ‘name’, ‘gender’, ‘age’, ‘bio’ in ‘person’ table with as many as 100 or even more columns, to maximize performance, they are best to be defined as:

  1. name – CHAR(70)
  2. gender – TINYINT(1)
  3. age – TINYINT(2)
  4. bio – TEXT

The idea is to define columns as small as possible and in fixed length where reasonably possible. Dynamic columns should be to the end of the table structure so fixed length columns ALL come before them. MySQL would then have a better time predicting the position of a column to perform select or update, otherwise it would have to read through the entire row byte by byte to find it.

It goes without saying this would introduce tremendous disk storage wasted with large amount of rows, but as you want performance I guess that would be the cost.

Another tip is as you go along you would find columns that are much more frequently used (selected or updated) than the others, you should separate them into another table to form a one to one relationship to the other table that contains infrequent used columns and perform queries with less columns involved.

SQL / MySQL Tips and Tutorials

MySQL: Incorrect key file error AND InnoDB: Error: unable to create temporary file; errno: 2

When I encounter the incorrect key file for table error, it’s almost certainly because the disk is full and MySQL can’t write to the tmpdir. The solution is simple, just move tmpdir to another partition with enough disk space.

Open my.ini and change tmpdir to another directory with enough disk space. Restart MySQL and that should do it.

However on Windows, such as for WAMP, you need to make sure you use slashes (/) rather than backslashes (\) in the path for tmpdir in my.ini, or it would be this error and mysqld would simply refuse to start:

InnoDB: Error: unable to create temporary file; errno: 2

Hosting Tips & Deals Manage Your Own Server SQL / MySQL Tips and Tutorials

Make phpMyAdmin Show Exact Number of Records for InnoDB Tables

By default, phpMyAdmin shows an estimated number of records for InnoDB tables that have more than 20,000 rows. It can vary by every fresh as much as 50% or even more. Makes it hard to get an exact number of records for the tables as we have to explicitly run an SQL query to do that:

SELECT COUNT(*) FROM table_name

While it would make it a bit slower for phpMyAdmin to open up the database tables list page because it has to count the exact total number of records of each table in the database by this query, it’s worth it for some of us as we want to know the exact number of rows the InnoDB table currently has. phpMyAdmin does it for MyISAM tables, and we want it to do the same with InnoDB tables.

So how can we make phpMyAdmin show exact number of records for InnoDB tables?

Simple. Just open the in your phpMyAdmin installation directory and add this line:

$cfg['MaxExactCount'] = 2000000;

This configuration parameter sets the threshold that phpMyAdmin executes COUNT(*) against the table.

In this case, if the total number of recrods in this InnoDB table is larger than 2,000,000, the Rows count will be estimated, starting with a wave sign, ~; on the other hand, if the total number of records in this InnoDB table is smaller than 2,000,000, phpMyAdmin will run COUNT(*) against the table so an exact Rows count is displayed.

You get the idea.

To make phpMyAdmin show exact number of rows on all InnoDB tables, simply make the parameter $cfg[‘MaxExactCount’] large enough.

SQL / MySQL Tips and Tutorials

MySQL: LOAD DATA LOCAL INFILE only imports 1 or 2 rows?

It is common to import CSV files into MySQL database. You can do this with phpMyAdmin with small CSV files but with large ones, you would probably encounter the memory error and had to switch to MySQL command line “LOAD DATA LOCAL INFILE” to do the job.

It looks like something like this:

INTO TABLE `your_table`
(field1, field2, field3)

And then you encounter another problem that it only imports the first 1 or 2 rows and then it stops. After some researching and trying, I was sure it’s something to do with the “LINES TERMINATED BY” directive. Depending on the platform that the CSV file is created on, the line delimiter may be

  1. \n
  2. \r\n
  3. \r

And you need to be correct on the line delimiter to properly parse the CSV file.

So the solution is to try them all one by one and see which one of them works. Chances are, one of them would make the whole command successfully import ALL rows.

Another simple approach is to deprive the whole command of the “LINES TERMINATED BY” directive and let MySQL do the call. It’ll probably detect things right but in my case, this doesn’t work but specifying ‘\r’.

SQL / MySQL Tips and Tutorials

MySQL Performance Tips

MySQLI’m not an expert but I’ve had my own errors and trials along the way so here I am. These are all the tips I believe you should use when you are optimizing for MySQL database performance. Some stuff may be very handy, but they may not be good for performance.


  1. Normalize first, and de-normalize where appropriate. Table joins are never lags in performance. Use them!
  2. Choose the appropriate collation / charset. UTF16 is omnipotent but it requires 2 times the storage. UTF8 delivers more characters but it’s slower than latin1. Unless it’s necessary, use latin1.
  3. utf8_general_ci is slightly faster than utf8_unicode_ci, thus better in performance.
  4. Use NOT NULL wherever possible.
  5. Use indexes. Create indexes by analyzing the SELECTs you are doing on the table.
  6. Indexing increases performance in selecting, yet decreases performance in inserting. So don’t index everything but only those necessary. And don’t make duplicate indexes.


  1. Use smallest data types possible.
  2. IN (…) is a total fail. Avoid it wherever possible.
  3. ORDER BY RAND() is a fail too.
  4. When you need to SELECT … to make sure there’s no duplicate unique index values before inserting something, use INSERT … ON DUPLICATE KEY UPDATE … instead.
  5. If your app writes a lot, use InnoDB; if it reads a lot, use MyISAM. Here’s a comparison.
  6. Never use LIMIT xxxx1, xxxx2 with large tables especially when offset xxxx1 is a pretty large number. Instead, give a more strict WHERE … clause with LIMIT xxxx2.
  7. If a feature is deprecated, stop using it.
  8. Avoid wildcards % or _ at the beginning of LIKE queries, e.g. LIKE ‘%bloom’ – this should be avoided. For instance, you should store email addresses reversed (REVERSED()) so it’s faster to search by domain.
  9. If you have a large set of data rows to be inserted at once, create a combined INSERT query string in PHP first – that is, to bulk insert them in batches rather than one by one. LOAD DATA is also a better option than individual INSERT.
  10. Prefer GROUP BY over DISTINCT.
  11. Frequently visit Slow Query Log.
  12. Avoid calculated values in WHERE … , LIMIT … , ORDER BY … , etc.
  13. Use EXPLAIN … to get an idea of how MySQL is doing with your queries thus better optimize it. For example, it would suggest the indexes you should have created.
  14. Don’t SELECT * … on everything, SELECT only what you need.
  15. Be wary of lots of small queries that may be triggered in loops. Instead, combine them and use one large query wherever possible.

Storage Engines

  1. Know the pros and cons of different storage engines and use them accordingly. But try them out in the test environment before making the decision.
  2. Archive old data such as logs in ARCHIVE tables or MERGE tables.
  3. BLACKHOLE engine is very fast for busy things like logs.


  1. Increase the default key buffer size: key_buffer = 128M
  2. Increase the default number of tables that can be kept open: table_cache = 128
  3. Increase the default sort buffer size:
    sort_buffer_size = 32M
    myisam_sort_buffer_size = 32M
  4. Disable binary logging that is for data replication by commenting out the line like this:  # log-bin=mysql-bin

What to read from here:

  1. Indexing:
  2. “LIMIT” Optimization:
  3. “ORDER BY” Optimization
  4. Efficient Paging:
  5. Configuration Tweaks:
SQL / MySQL Tips and Tutorials

MySQL: Get the exact size of a database by SQL query

phpMyAdmin doesn’t show the exact size in MB when the size of your database exceeds 1GB. It just shows something like 4.2GB, truncating everything out in the 100MB precision. So is it possible to get the database size in MB in exact numbers using MySQL query?

Yes, it is:


Just change YOUR_DATABASE_NAME_HERE into the name of your database. And it would prints out something like this:

| Size       |
| 4906.79 MB |
1 row in set (0.05 sec)

You can also get the size of a specific table. Read here:

SQL / MySQL Tips and Tutorials

MySQL: Find non-ASCII characters in a table column

ASCII is the most fundamental character set that has been around since the early days of command line interfaces. There are 128 (0 – 127) most basic ASCII characters such as a-z, A-Z, 0-9, and all the printable punctuations you can type out by a single strike of your keyboard.

As all ASCII characters have an internal decimal value of 0 – 127, which is 0x00 – 0x0F in heximal values, you can find all the non-ASCII characters in my_column by query:

SELECT * FROM my_table WHERE NOT HEX(my_column) REGEXP '^([0-7][0-9A-F])*$';

On the other hand, if you wish to find all records that a certain column (my_column) contains ASCII characters:

SELECT * FROM my_table WHERE HEX(my_column) REGEXP '^([0-7][0-9A-F])*$';
SQL / MySQL Tips and Tutorials

MySQL: How to export a database / table to XML?

You can export any MySQL database or table into an XML file by the exporting capabilities of phpMyAdmin, the web interface of MySQL. The problem with this approach, however, is that with large tables, you may have to manually export the table more than once by sections into several sequential XML files.

A better approach is by native MySQL command line that will create and store all entries of the table into one XML file:

mysql -u db_user -p db_name --xml -e "SELECT * FROM table_name" > table_name.xml

Wherein db_user is your MySQL server user, db_name is the name of the database and table_name is the name of the table that you would like exported to XML. The resulted XML will be stored in table_name.xml.

Note that this is different from mysqldump in that it’s basically executing a query by the –e switch and output the results in XML (–xml). The results is then directed to the out file rather than displayed on terminal screen.

SQL / MySQL Tips and Tutorials

MySQL: Export Table to CSV Text Files for Excel

MySQL tables can be exported to SQL dump file which is basically a text file of SQL queries that can be used to import the table back into database. To export MySQL tables into other formats such as CSV, phpMyAdmin proves to be very handy if you have changed the execution timeout in seconds to zero (so it never times out) – or it won’t work with large tables. However, there’s another way to do this in native SQL query and it works until the end of a very large table:

SELECT * FROM mytable INTO OUTFILE "c:/mytable.csv"

The INTO OUTFILE command in this MySQL query will store all rows selected from the table mytable into the text file c:/mytable.csv in the form:


Now you can open and use the CSV file in another application such as Excel and manipulate it any way you like.

If you need another format, just change the query accordingly.

Add column / field headers at the beginning of the CSV file

To add column names at the beginning of the CSV file so each field is identified, use this SQL snippet:

SELECT 'id', 'name', 'age' UNION
SELECT * FROM mytable INTO OUTFILE "c:/mytable.csv"

That is, adding “SELECT ‘id’, ‘title’, ‘slug’ UNION”. And you will have:


The awkward thing is you have to manually add the field names one by one. For now, I know no option other than this that can add the column names before all the records when you are dumping CSV files from MySQL, except with phpMyAdmin.