Introduction In this article, we are going to see how we can sort an SQL query result set using an ORDER BY clause that takes a RANDOM function provided by a database-specific function. This is a very handy trick, especially when you want to shuffle a given result set. Note that sorting a large result set using a RANDOM function might turn out to be very slow, so make sure you do that on small result sets. If you have to shuffle a large result set and limit it afterward, then it’s... Read More
1 Older Entries »
In my post yesterday, I shared a little known trick for sorting NULLs last when using ORDER BY ASC.
To summarize briefly, NULLs are treated as less than 0 when used in ORDER BY, However, sometimes you do not want that behavior, and you need the NULLs listed last, even though you want your numbers in ascending order.
So a query like the following returns the NULLs first (expected behavior):
SELECT * FROM t1 ORDER BY col1 ASC, col2 ASC; +--------+------+ | col1 | col2 | +--------+------+ | apple | NULL | | apple | 5 | | apple | 10 | | banana | NULL | | banana | 5 | | banana | 10 | +--------+------+
The trick I mentioned in my post is to rewrite the query like:
SELECT * FROM t1 ORDER BY col1 ASC, -col2 DESC;
The difference is that we added a minus sign (-) in front of the …[Read more]
I was working on a seemingly basic query the other day where the user needed to have an INT column listed in ascending order (i.e., 1, 2, 3, …).
However, the tricky part came in because the column allowed NULLs and the user needed the NULLs to be listed last, not first, which is the default behavior in both MariaDB and MySQL.
We first devised a somewhat convoluted solution where we used ISNULL() first in the ORDER BY, and then the column, but that wasn’t ideal since it added an additional check for each row in the ORDER BY, which we wanted to avoid in a query returning ~5M rows.
To illustrate, a normal query just sorting in ASC order returned:
MariaDB> SELECT * FROM t1 ORDER BY col1 ASC, col3 ASC; +--------+--------+------+ | col1 | col2 | col3 | +--------+--------+------+ | apple | yellow | NULL | | apple | red | 5 | | apple | green | 10 | | banana | brown | NULL | | banana | green | 5 | | …[Read more]
The future MySQL 8.0 will (probably) have a great new feature: support for index sort order on disk (i.e., indexes can be physically sorted in descending order). In the MySQL 8.0 Labs release (new optimizer preview), when you create an index you can specify the order “asc” or “desc”, and it will be supported (for B-Tree indexes). That can be especially helpful for queries like “SELECT … ORDER BY event_date DESC, name ASC LIMIT 10″ (ORDER BY clause with ASC and DESC sort).
MySQL 5.6 and 5.7 Index Order
Actually, the support for this syntax ( …[Read more]
Quite often, “simple” logic can work better than complex logic as chasing all the corner cases can just make things worse – but sometimes, logic can be too simple.
Everything must be made as simple as possible, but no
— Albert Einstein / Roger Sessions
Shard-Query inserts data into a “coordinator” table when answering queries. When there is a GROUP BY on the original query, the coordinator table contains a UNIQUE KEY over the GROUP BY attributes. Shard-Query uses INSERT .. ON DUPLICATE KEY UPDATE in combination with bulk insert (insert into … values (),(),() ) when inserting into the table.
For what would normally be efficiency sake, Shard-Query sends queries to the shards using ORDER BY NULL which disables the filesort operation. Of course, this often results in the rows being sent back from the shards in random order.
Because the results are in random order, the bulk insertion that the worker does into the coordinator table can deadlock with other worker threads when using InnoDB or TokuDB as the coordinator table. Right now I’ve just been using MyISAM for the coordinator table, which serializes queries at the bulk insert stage. Having to insert the …[Read more]
You might find a warning like the below in your error log:
130522 17:54:18 [Warning] Unsafe statement written to the
binary log using statement format since BINLOG_FORMAT =
STATEMENT. Statements writing to a table with an auto-increment
column after selecting from another table are unsafe because the
order in which rows are retrieved determines what (if any) rows
will be written. This order cannot be predicted and may differ on
master and the slave.
Statement: INSERT INTO tbl2 SELECT * FROM tbl1 WHERE col IN (417,523)
What do MariaDB and MySQL mean with this warning? The server can’t guarantee that this exact query, with STATEMENT based replication, will always yield identical results on the slave.
Does that mean that you have to use ROW based (or MIXED) replication? Possibly, but not necessarily.
For this type of query, it primarily refers to the fact that without …[Read more]
The easiest way to generate random rows in MySQL is to use the ORDER BY RAND() clause. SELECT col1 FROM tbl ORDER BY RAND() LIMIT 10; This can work fine for small tables. However, for big table, it will have a serious performance problem as in order to generate the list of random rows, MySQL […]
There are two PRIMARY directions to sort data in SQL: Ascending
(ASC) and Descending DESC.
When these two sort definitions are put together in a single statement a filesort is produced.
Why do we want to avoid filesorts?
Filesorts are bad. 1st they tickle a thread based buffer called sort_buffer_size. Additionally filesorts reads the data twice, unless max_length_for_sort_data limit is reached and as a result the Filesort runs slower to reduce disk I/O. If you want filesorts to run faster at the expense of the disk increase the default max_length_for_sort_data. You can read the filesort algorithm here.
So, here is an example
CREATE TABLE `ABCD` (
`A` int(10) unsigned NOT NULL default '0',
`B` int(10) unsigned NOT NULL default '0',
`C` int(10) unsigned NOT NULL …
I've blogged before about the new SQL synthax which is becoming available in databases and how it helps solving questions which are increasingly common.Now it's time for another example, something which doesn't come up often in a reporting environment as most tools have this feature, but can be a problem if you're building your output with a scipting language.Adding a "Total" row at the end of
1 Older Entries »