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. NoSQL vs. SQL Documents vs. Tables MongoDB vs. MySQL 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, …

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

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: Define your column as small as possible. 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 …

Optimize for Large Number of Columns in a Wide MySQL Table Read More »

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 …

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

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 …

Make phpMyAdmin Show Exact Number of Records for InnoDB Tables Read More »

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: LOAD DATA …

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

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: SELECT CONCAT(sum(ROUND(((DATA_LENGTH + INDEX_LENGTH – DATA_FREE) …

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

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 …

MySQL: Find non-ASCII characters in a table column Read More »

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 …

MySQL: How to export a database / table to XML? Read More »

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 …

MySQL: Export Table to CSV Text Files for Excel Read More »

MySQL log files are taking a lot of disk space – How to disable and delete them?

I have WAMP server installed on my local computer for PHP and MySQL development. After using it for a while, the MySQL installation folder has run up some seriously huge log files that are taking enormous amount of disk space. We are talking 10s of GB here. The file names are like ibdata1, mysql-bin.000017, etc.. …

MySQL log files are taking a lot of disk space – How to disable and delete them? Read More »

MySQL: Change Default Character Set or Default Collation in phpMyAdmin

It can be annoying when MySQL imports your UTF8 database (which contains exotic characters other than those in English) in the default character set of latin1_swedish_ci, jeopardizing the text content. It is also annoying when phpMyAdmin does the same and when you forgot to set the collation to utf8_general_ci for the new database which is …

MySQL: Change Default Character Set or Default Collation in phpMyAdmin Read More »

MySQL: Check if a table exists

When I was creating an installation script, I needed to check if a table exists to make sure the installation had not been performed yet. How did I do that? I use this simple query to get whether a table exists in the specified database: SELECT table_name FROM information_schema.tables WHERE table_schema = ‘db_name’ AND table_name …

MySQL: Check if a table exists Read More »

MySQL logic operators: How to use AND, OR together in WHERE clauses in one query?

AND and OR are common logic operators across all programming languages and in MySQL, they mean the same thing. A statement (a.k.a, a condition in any WHERE clauses such as id > 100) can be either true or false. A group / combination of statements can be true or false, depending on their own values …

MySQL logic operators: How to use AND, OR together in WHERE clauses in one query? Read More »

How to bring down / optimize memory usage in your unmanaged Linux VPS box and avoid OOM (Out Of Memory) errors?

The other day I was very upset about some extraordinary down times of my unmanaged VPS box at Linode. As it’s unmanaged, support staff at Linode are not responsible for the failures. I contacted them and they told me it’s OOM (Out Of Memory), pointing me to the right documentation to figure out how to …

How to bring down / optimize memory usage in your unmanaged Linux VPS box and avoid OOM (Out Of Memory) errors? Read More »

A few database security tips – things to do to effectively protect MySQL databases

I’d like to share with you some tips about hardening the database part of your application. Here are a few things you can do in protecting the databases from being compromised in security: Create separate users with ONLY necessary privileges (as few as possible) to connect to the database for common daily tasks. Never use …

A few database security tips – things to do to effectively protect MySQL databases Read More »

MySQL: How to backup ALL databases as root with mysqldump at once?

When you have a VPS or dedicated server to manage, typically you’d have a bunch of different mysql users granted the privileges of every particular database for the sake of security. While this works well in segregating the privileges and preventing hackers from gaining access to all databases upon the compromise of only one mysql …

MySQL: How to backup ALL databases as root with mysqldump at once? Read More »

MySQL: How to change or convert MyISAM to InnoDB or vice versa?

After we have discussed the advantages and disadvantages of MyISAM and InnoDB, you might want to convert them back and forth and see the performance differences yourself. For small website, the difference may be minor but for a large popular application, MyISAM tables yield better performance results in selecting data records InnoDB tables give better …

MySQL: How to change or convert MyISAM to InnoDB or vice versa? Read More »

MySQL Engines: InnoDB vs. MyISAM – A Comparison of Pros and Cons

The 2 major types of table storage engines for MySQL databases are InnoDB and MyISAM. To summarize the differences of features and performance, InnoDB is newer while MyISAM is older. InnoDB is more complex while MyISAM is simpler. InnoDB is more strict in data integrity while MyISAM is loose. InnoDB implements row-level lock for inserting …

MySQL Engines: InnoDB vs. MyISAM – A Comparison of Pros and Cons Read More »