Categories
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.

Categories
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

Categories
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 config.inc.php 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.

Categories
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:

LOAD DATA LOCAL INFILE 'your.csv'
INTO TABLE `your_table`
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(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’.

Categories
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.

Design

  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.

Querying

  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.

my.ini

  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: http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
  2. “LIMIT” Optimization: http://dev.mysql.com/doc/refman/5.0/en/limit-optimization.html
  3. “ORDER BY” Optimizationhttp://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html
  4. Efficient Paging: http://www.slideshare.net/suratbhati/efficient-pagination-using-mysql-6187107
  5. Configuration Tweaks: http://docs.cellblue.nl/2007/03/17/easy-mysql-performance-tweaks/
Categories
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:

SELECT CONCAT(sum(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2))," MB") AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA like 'YOUR_DATABASE_NAME_HERE%' ;

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: http://www.novell.com/communities/node/8706/check-mysql-database-size-using-sql-query

Categories
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])*$';
Categories
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.

Categories
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"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY "\n";

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:

"1","Anthony","24"
"2","Rachel","27"

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"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY "\n";

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

"id","name","age"
"1","Anthony","24"
"2","Rachel","27"

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.

Categories
SQL / MySQL Tips and Tutorials

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.. It appears it’s because I frequently have large databases in and out by the ‘mysql’ and ‘mysqldump’ command, building up large stacks of data importing and exporting logs for data rollback and recovery or something.

Simply deleting the log files may result in problems such as being unable to start MySQL again. Tried that and I had to reinstall it to make it work again.

After some searching, I’ve found this query to solve my problem. Just execute it after you have logged in MySQL via command line – of course you will need the root privileges:

PURGE BINARY LOGS BEFORE '2036-12-12 12:12:12';

Something like that would purge and delete all the huge logs to free the disk. Just make the date time to be as sci-fi as possible so that all log files are purged.