Tag Archives: mysql

HeidiSQL: The Best MySQL GUI Client?

We use MySQL at work. I am disappointed when I see even experienced developers using MySQL command line client or even MySQL workbench to run their queries. There is a nice GUI client, HeidiSQL, which is completely free and provides auto-complete features. The moment you alias a table name in HeidiSQL, it helps you write your queries much faster. Just like the Eclipse IDE, HeidiSQL uses Ctrl+Space for auto-suggest. You can have it auto-suggest column names, or even table and database names. It helps me save several minutes every day in writing SQL queries.

Here is an example, where I pressed Ctrl+Space after the table alias lo for the table list_options:

HeidiSQL Auto Suggest

If you are also a purist like me when writing SQL and want to upper-case all the keywords in your query to make them look pleasing, just highlight your query and press Ctrl+F8. (I have always preferred lower-case for database, table and column names and upper-case for SQL keywords.)

There is also another GUI client, SQLYog, which automatically upper-cases SQL keywords in your query as you type. But if you need its auto-complete feature, you need to pay for it. For me, the upper-casing is not as much of importance as auto-suggest.

From among the few different MySQL clients I have tried, HeidiSQL has been the one that has worked the best for me!

BIGINT UNSIGNED value is out of range

Ran into a strange issue with a MySQL query.

Here is a table I got:

The main point to notice is that there are two UNSIGNED columns: num_up_votes and num_down_votes.

I tried to run this query:

and MySQL gave me this error:

ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '('0' - '18')'

Could not understand why MySQL threw error for this. Of course, the result is negative, but I am not trying to store anything into an UNSIGNED column.

A google search led me to the MySQL manual’s out of range and overflow page.

where it states

Subtraction between integer values, where one is of type UNSIGNED, produces an unsigned result by default.

This is a really strange behavior!

For now, there are two solutions:

  1. Use CAST with SIGNED as follows in the SQL itself:

  2. If you are using an interactive client, you can set sql_mode like this:

    which then allows subtraction between unsigned column values when the difference is negative.

Working with Comma Separated Values in MySQL

To begin with, a word of caution on comma-separated strings in MySQL.

Let us say we want to store a list of related IDs for a given ID. (Think similar products or documents.)

Rather than creating a table like this:

with the intent of storing comma-separated values in related_ids column, it is much better to create a table like this:

This second table facilitates:

  1. more queries
  2. more indexes
  3. data consistency

For example, it helps us keep a unique index on (related_id, id) pairs to avoid duplicate related_ids for an id. The index is also used for look-ups by related_id. (Notice that we could have also kept a unique index on (id, related_id) pair to enforce data consistency and a separate index on related_id for queries. But this is inefficient. The unique index on (id, related_id) will only help us with data consistency and not with look-ups by related_id, for which we need the additional index on just related_id.)

However, people are forced to store comma-separated values for scaling reasons. For example, in my company, we have a table where we store for a given ID, fifty related IDs. The number of IDs in our system is about 500K. If we went with the second table, we would be storing 25M rows. While reads are much more frequent than writes, queries do get really slow with 25M rows. So we had to use the first table.


MySQL does have some nice functions to work with comma-separated values which we will see here.

Below, I am using @x, @y, @css (comma-separated string), etc., for SQL variables. You can set them like

to play around with the code.

PROBLEM: Given a comma-separated string, find the number of values in it.
SOLUTION: We just need to count the number of commas in the string and add one to the result to get the number of values. Can be done as:

REPLACE( @css, ',', '' ) simply replaces the commas with empty strings. So if @css is a,b,c,d then REPLACE( @css, ',', '' ) is simply abcd, so subtracting its length from the original string gives us the number of commas and we add 1 to that to get the number of values.

PROBLEM: Get the list of all IDs that have a given ID @x as a related ID.
SOLUTION: Use FIND_IN_SET. FIND_IN_SET(@x, @css) gives the index of @x in @css. Note that the index begins with 1. So for example,

gives the result 3.

So using this SQL:

will give us all the IDs that have @x as a related ID. (Notice however that this query has to perform a full table scan and cannot use indexes.)

PROBLEM: Find all those IDs that got @x as the @nth related ID.
SOLUTION:

PROBLEM: Find the @n‘th related ID for a given ID @x.
SOLUTION: We need to learn about another function: SUBSTRING_INDEX.

SUBSTRING_INDEX(@css, @delimiter, @n) will give the substring of @css upto the @nth @delimiter. For example:

will give us the sub-string of a,b,c,d,e,f,g upto the 4th comma i.e. it will output:

A very nice thing about SUBSTRING_INDEX is that negative indices work as well. They just give us the sub-string from the right side upto the @nth delimiter. For example:

will output

So to get the 5th value in a comma-separated string, we will use:

which will output ‘e’.

So our problem of finding @nth related ID of a given ID @x can be solved like this:

WARNING:
One caveat with SUBSTRING_INDEX is that if the number of values in our comma separated string is less than @n, then it will output the entire string. So for example:

will output a,b,c,d since the 5th occurrence of comma never occurred.

So doing:

will give us d which is wrong.

If we are unsure whether our comma-separated string has at least @n values, then we can first check if the number of values is greater than or equal to @n and then use the above, else return the empty string:

Speeding up MySQL data restore

A typical task we routinely do is to take the dump of our production databases and load them on the MySQL server running on our local machines. We mostly have tables using only one engine in a database – for our write-heavy and transactional storage we use InnoDB tables in one database and for read-only tables we use MyISAM tables in another database. The InnoDb database dump is about 20GB and the MyISAM one about 30 GB. We use mysqldump on one of our production slave boxes to get the dumps and make them available for devs to download it.

Once the data dumps are downloaded, data restore on our local boxes is done with the following command:
mysql -uUSER -pPASSWORD DATABASE < DUMPFILE.sql

We can tweak the following variables in MySQL’s my.cnf for data restore speed-ups:

MyISAM – key_buffer_size (max. allowed = 4G)
Set this to 30% of the memory on your box. I have an 8GB box, so I set it to 2400M.

InnoDB – innodb_flush_log_at_trx_commit
temporarily set this to 2. After import is done, revert it to 1.

References:
MySQL key_buffer_size
MySQL flush_log_at_trx_commit
Serverfault
Stackoverflow