query | Z-Car

MySQL – ORDER BY does not sort data properly…

By Prodromus December 28, 2010 Errors, MySQL, Optimization 4 Comments

Imagine a developer working late at night trying to complete some simple queries for a report due the next day. No matter what she does, the resulting query will just not sort properly. Is this some undiscovered MySQL bug? No wonder this software is free, MySQL is broke and can’t even perform a basic sort. It looks simple, sort the data in a table:

mysql> SELECT id, technology from Enigma;
+——+————-+
| Id | technology |
+——+————-+
| 1 | PHP |
| 2 | LINUX |
| 3 | MySQL |
+——+————-+

To sort the records based on the technology, do an ORDER BY on column Technology:

mysql> SELECT id, technology from Enigma ORDER BY Technology;
+——+————-+
| Id | technology |
+——+————-+
| 1 | PHP |
| 2 | LINUX |
| 3 | MySQL |
+——+————-+

It seems that the server does not understand what I have asked, or I have not mastered the entire alphabet …
Let’s add the clause CSA (just in case!)

mysql> SELECT id, technology from Enigma ORDER BY ASC Technology;
+——+————-+
| Id | technology |
+——+————-+
| 1 | PHP |
| 2 | LINUX |
| 3 | MySQL |
+——+————-+

That does not seem to be any better! MySQL does not sort the data as I wish, am I going crazy? Should I change my RDBMS?
Don’t worry, a careful look at the table structure allows us to see things clearly.

mysql> SHOW CREATE TABLE enigma;

Table: enigma
Create Table: CREATE TABLE `Enigma` (
`Id` int (11) DEFAULT NULL,
`Technology` enum (‘PHP’, ‘Linux’, ‘MySQL’) DEFAULT NULL
) ENGINE = MyISAM DEFAULT CHARSET = latin1

The column technology is of a type ENUM. The Enum field is stored as an integer, and that is the index that MySQL uses for sorting. In other words, 1 is PHP, 3 is MySQL, and 2 is Linux, so sorting does works properly, just not as expected when the Enum data field is storing character data. So how do you get the desired result? Simply force MySQL to use the value string and not index when sorting. This can be done by using either the Concat() or Cast() functions, as follows:

function concat ()
mysql> SELECT id, technology from Enigma ORDER BY concat (technology);
+——+————-+
| Id | technology |
+——+————-+
| 2 | LINUX |
| 3 | MySQL |
| 1 | PHP |
+——+————-+
function cast ()
mysql> SELECT id, technology from Enigma ORDER BY cast (technology as char);
+——+————-+
| id | Technology |
+—– -+————-+
| 2 | LINUX |
| 3 | MySQL |
| 1 | PHP |
+——+———– – +

Top My.CNF Optimizations for Innodb

By Prodromus August 20, 2010 MySQL, Optimization No Comments

These are the four parameters which will have the most affect on the performance of MySQL 5.1x and Innodb. If you are suffering from poor performance, try changing the following settings. Remember, each individual situation will vary, and in many cases, the actual design of your queries will have more to do with your overall performance than any system tuning tricks. The best tool I have found to capture, review, and analyse query performance for MySQL is Jet Profiler for MySQL. If you need help optimizing your queries, let us know.

innodb_buffer_pool_size =8G
Set this to ~80% of free server memory. For example, if you have a dedicated MySQL server with 10G, set to 8G

innodb_flush_log_at_trx_commit =0
Setting this to 0 will have a huge performance improvement, however your data is at somewhat more if you have a hardware failure

sync_binlog =0
Setting this to 0 will have a huge performance improvement, however your data is at somewhat more if you have a hardware failure

innodb_flush_method=O_DIRECT
On many systems, this will provide a performance improvement. However, this can actually have a negative affect, so make sure you test appropriately.

Show Processlist and Show Full Processlist – MySQL Tip

By Prodromus August 19, 2010 MySQL No Comments

Everyone knows that using the MySQL command Show Processlist will display all current connections, like the following :

What is little known however is that the Show Full Processlist command will display the full query in the Info column. By default, Show Processlist will only display the first 100 characters. One thing to keep in mind about the Info line is that while the statement (query) might be the one sent to the server, it will display the innermost statement if the statement executes other statements.

MySQL – Should you put an index on a Boolean field to help query performance?

By Prodromus June 14, 2010 MySQL, Query Optimization 2 Comments

I am often asked if it makes sense to place an index on a Boolean field in order to improve query performance. In general, because a boolean value can only have three values (True, False, Null), this low cardinality would suggest that adding an index will not help performance, as the query optimizer will still usually perform a table-scan if you have an even distribution of values within your DB.

One situation in which an index on a boolean field (or other low cardinality field) might be useful is if there are relatively few of one of the values, for example 5 True values in a table of millions of records and you are searching for those few values on a regular basis.

However, you might index a boolean value on a combination of fields. Indexing on a single Boolean might be pointless, because there’s only 2 (or 3) values. However, indexing on 16 boolean values has the potential of 2^16 values. It might help to make a combined index but you should understand how the combined index can and cannot be used and be aware that the order of the columns matters.

In general, you should always profile your system to see if there are queries that are too slow and consider adding another index to handle those queries. Sometimes a single combined index can be used for multiple queries and others time you will need to make an index for each type of query. Remember that adding indexes slows down modifications to the data so it is possible to have too many indexes. There is always a trade-off when creating multiple indexes.

MySQL – SQL Injection, and how to Prevent it

By Prodromus June 11, 2010 MySQL, Scripting No Comments

SQL injection vulnerabilities are often been described as the most serious threat for Web applications, regardless of what language they are written in . Web applications that are vulnerable to SQL injection may allow an attacker to gain complete access to their underlying databases, regardless of if they are using MySQL Server, SQL Server, or Oracle.

SQL-injection attacks are those in which data provided by the user is included in an SQL query in such a way that part of the user’s input is treated as SQL code that is executed on the server. By using this technique, an attacker can submit SQL commands directly to the database. These attacks are a serious threat to any Web application that receives input from users and passes it into SQL queries to an underlying database server. If user input is not santised properly, web applications may result in SQL Injection attacks that allow hackers to view information from the database and/or even wipe it out.

To defend against SQL Injection attacks, user input must not directly be embedded into SQL statements that are executed on the server. Instead, you must use parameterized statements, and Escaping functions to check user input.

Various resource for addressing SQL Injection are as follows:

Bobby Tables provides real-world practical code for addressing SQL Injection – http://bobby-tables.com/
Michal Daw’s Blog Page outlines various SQL Injection vectors – http://michaeldaw.org/sql-injection-cheat-sheet

MySQL – Disabling Binary Logging for INSERT and UPDATE

By Prodromus June 2, 2010 MySQL, Replication 1 Comment

I have run into many situations where there would be a benefit to not replicate a large INSERT or UPDATE from the MASTER to SLAVE in order to not block replication for a long period of time. Luckily MySQL provides a means to disable binary logging for your current session. Simply execute SET SQL_BIN_LOG=0 before the SQL Statement that you do not want logged to the BINLOG. This is a session variable, meaning it will be re-enabled when you close the session, or you can set it back to 1.

In order to keep the Slave in-sync, you will need to execute the same query on the Slave. You can also use mk-table-sync from the Maatkit toolkit to re-sync your table data at a later time.

« Previous