MySQL Query – How to ORDER BY Both ASC and DESC

MySQLorder-byselect

I'm using this sql query to get the largest and smallest value of electricity (value of the tag electricity is incremental).

SELECT x.value AS 'min',
       y.value AS 'max' FROM
  ( SELECT `value`
   FROM `measurements`
   WHERE `tag`= 'electricity'
     AND `timestamp` > '2017-07-14 13:00:00'
     AND `timestamp` < '2017-08-25 13:00:00'
   ORDER BY `timestamp` ASC
   LIMIT 1) AS x,

  (SELECT `value`
   FROM `measurements`
   WHERE `tag`= 'electricity'
     AND `timestamp` > '2017-07-14 13:00:00'
     AND `timestamp` < '2017-08-25 13:00:00'
   ORDER BY `timestamp` DESC
   LIMIT 1) AS y

Is it possible reduce the query size to use only one SELECT but order it twice?

NOTE: Due to a large data-set (tested 100 million rows) using MIN and MAX the query takes more than 30 seconds. Doing it this way it returns instantly.

Per request:

Query above
+----+-------------+--------------+--------+---------------------------+-----------------+---------+------+---------+----------+-------------+
| id | select_type | table        | type   | possible_keys             | key             | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+--------------+--------+---------------------------+-----------------+---------+------+---------+----------+-------------+
|  1 | PRIMARY     | <derived2>   | system | NULL                      | NULL            | NULL    | NULL |       1 |   100.00 |             |
|  1 | PRIMARY     | <derived3>   | system | NULL                      | NULL            | NULL    | NULL |       1 |   100.00 |             |
|  3 | DERIVED     | measurements | range  | tag_index,timestamp_index | timestamp_index | 8       | NULL | 9418306 |     2.01 | Using where |
|  2 | DERIVED     | measurements | range  | tag_index,timestamp_index | timestamp_index | 8       | NULL | 9418306 |     2.01 | Using where |
+----+-------------+--------------+--------+---------------------------+-----------------+---------+------+---------+----------+-------------+


+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000030 |
| Waiting for query cache lock   | 0.000005 |
| checking query cache for query | 0.000965 |
| checking permissions           | 0.000010 |
| checking permissions           | 0.000008 |
| Opening tables                 | 0.000046 |
| System lock                    | 0.000132 |
| optimizing                     | 0.000020 |
| statistics                     | 0.000316 |
| preparing                      | 0.000032 |
| executing                      | 0.000005 |
| Sorting result                 | 0.000031 |
| Sending data                   | 0.000141 |
| optimizing                     | 0.000012 |
| statistics                     | 0.000256 |
| preparing                      | 0.000019 |
| executing                      | 0.000004 |
| Sorting result                 | 0.000027 |
| Sending data                   | 0.000902 |
| Waiting for query cache lock   | 0.000007 |
| Sending data                   | 0.000030 |
| init                           | 0.000018 |
| optimizing                     | 0.000006 |
| statistics                     | 0.000011 |
| preparing                      | 0.000007 |
| executing                      | 0.000004 |
| Sending data                   | 0.000017 |
| end                            | 0.000005 |
| query end                      | 0.000006 |
| closing tables                 | 0.000004 |
| removing tmp table             | 0.000008 |
| closing tables                 | 0.000004 |
| removing tmp table             | 0.000005 |
| closing tables                 | 0.000013 |
| freeing items                  | 0.000015 |
| Waiting for query cache lock   | 0.000005 |
| freeing items                  | 0.000341 |
| Waiting for query cache lock   | 0.000006 |
| freeing items                  | 0.000004 |
| storing result in query cache  | 0.000006 |
| logging slow query             | 0.000004 |
| cleaning up                    | 0.000007 |
+--------------------------------+----------+

Query with MIN & MAX
 SELECT MAX(value), MIN(value) FROM measurements where `tag`= 'electricity' AND `timestamp` >= '2017-07-14 13:00:00' and `timestamp` < '2017-08-25 13:00:00' LIMIT 1;

+----+-------------+--------------+------+---------------------------+-----------+---------+-------+--------+----------+-------------+
| id | select_type | table        | type | possible_keys             | key       | key_len | ref   | rows   | filtered | Extra       |
+----+-------------+--------------+------+---------------------------+-----------+---------+-------+--------+----------+-------------+
|  1 | SIMPLE      | measurements | ref  | tag_index,timestamp_index | tag_index | 122     | const | 189664 |   100.00 | Using where |
+----+-------------+--------------+------+---------------------------+-----------+---------+-------+--------+----------+-------------+


+--------------------------------+-----------+
| Status                         | Duration  |
+--------------------------------+-----------+
| starting                       |  0.000034 |
| Waiting for query cache lock   |  0.000006 |
| checking query cache for query |  0.000109 |
| checking permissions           |  0.000011 |
| Opening tables                 |  0.000026 |
| System lock                    |  0.000014 |
| Waiting for query cache lock   |  0.000037 |
| init                           |  0.000059 |
| optimizing                     |  0.000031 |
| statistics                     |  0.000435 |
| preparing                      |  0.000030 |
| executing                      |  0.000010 |
| Sending data                   | 22.093571 |
| end                            |  0.000016 |
| query end                      |  0.000004 |
| closing tables                 |  0.000028 |
| freeing items                  |  0.000015 |
| Waiting for query cache lock   |  0.000003 |
| freeing items                  |  0.000361 |
| Waiting for query cache lock   |  0.000006 |
| freeing items                  |  0.000003 |
| storing result in query cache  |  0.000005 |
| logging slow query             |  0.000004 |
| logging slow query             |  0.000005 |
| cleaning up                    |  0.000004 |
+--------------------------------+-----------+


*************************** 1. row ***************************
       Table: measurements
Create Table: CREATE TABLE `measurements` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nad` int(11) NOT NULL,
  `tag` varchar(40) NOT NULL,
  `value` varchar(16) NOT NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `tag_index` (`tag`),
  KEY `timestamp_index` (`timestamp`),
  KEY `nad` (`nad`)
) ENGINE=MyISAM AUTO_INCREMENT=115261277 DEFAULT CHARSET=utf8

Best Answer

Simple answer, no. But as a fun exercise the following will work in MySQL (see further down for obvious downsides):

NOTE: The code was changed back to match your own, but was tested with different fields and tables and worked. min/max slowness confirmed.

DROP VIEW IF EXISTS simple_view;
CREATE VIEW simple_view AS SELECT `value`, `timestamp`
   FROM `measurements`
   WHERE 
     `timestamp` > '2017-07-14 13:00:00'
     AND `timestamp` < '2017-08-25 13:00:00';

SELECT x.value AS 'min', y.value AS 'max' 
FROM 
  ( SELECT * from simple_view order by `timestamp` ASC LIMIT 1) as x,
  ( SELECT * from simple_view order by `timestamp` DESC LIMIT 1) as y;

DROP VIEW IF EXISTS simple_view;

Obvious downsides include (but not limited to):

Views can be slow (in this case as fast as your original in testing)
You can only have one of these running at a time, unless you somehow make the view name unique each time.
It really doesn't save you much
You need to remember to clean up after yourself.

A better solution is to simplify the query and build it on the fly in a language outside of MySQL so you don't care about repetition.

Related Solutions

Mysql – Is index like key(timetime) usable when we query: SELECT * … ORDER BY timetime DESC

Yes, it does. MySQL can use the index and use it for scanning/seeking in either direction. You can read in MySQL documentation: How MySQL Uses Indexes

To sort or group a table if the sorting or grouping is done on a leftmost prefix of a usable key (for example, ORDER BY key_part1, key_part2). If all key parts are followed by DESC, the key is read in reverse order. See Section 8.13.9, “ORDER BY Optimization”, and Section 8.13.10, “GROUP BY Optimization”.

MySQL – Using Index for Both ASC and DESC on String Column

Although the syntax for CREATE INDEX supports an option for defining the index as ascending or descending, in the most common two storage engines (InnoDB and MyISAM), this index option is a no-op. There is no such thing as an "ascending" or "descending" index, they are just indexes and can be used for sorting in either direction.

But in the case you describe, where you have a multi-column index and you want to order by one column ascending and the other column descending, that's a different story. The sort order must go in the same direction for both columns if it uses an index.

(Not that every sort has to use an index. You can certainly run the query and let it sort matching rows with a filesort.)

To reverse the sort order of strings, you could add your own custom collation. https://dev.mysql.com/doc/refman/5.6/en/adding-collation.html

But you'd have to store your strings using that new collation, and rebuild your index. The index would only be useful for the reverse-sorting.

Re comment from @Cratylus:

A collation is what defines "A" as being before "B" and all the other ordering of letters. So if you define a collation that says that "B" is before "A", and use that collation in your column definition, then sorting on that column, or creating an index on the column, would be in the reverse order.

Note: the following doesn't create a proper collation, it just demonstrates the steps to create one.

Edit /usr/share/mysql/charsets/Index.xml and make this edit:

<charset name="latin1">

  . . . leave other collations alone . . .

  <collation name="latin1_reverse_ci" id="251">
    <order>Dutch</order>
    <order>English</order>
    <order>French</order>
    <order>German Duden</order>
    <order>Italian</order>
    <order>Latin</order>
    <order>Portuguese</order>
    <order>Spanish</order>
  </collation>

</charset>

Edit /usr/share/mysql/charsets/latin1.xml and make this edit:

<charset name="latin1">

. . . leave other collations alone . . .

<collation name="latin1_reverse_ci">
<map>
 AE B1 AC A2 A0 9E 9C 8D BE 8B 89 87 85 83 7F 59
 73 71 6F 6D 63 61 5F 5D 55 4F 4D 4B 49 47 45 43
 97 B1 AC A2 A0 9E 9C 8D BD 8B 89 87 85 83 7F 59
 73 71 6F 6D 63 61 5F 5D 55 4F 4D 4B 49 47 45 43
 FF FE FD FC FB FA F9 F8 F7 F6 F5 F4 F3 F2 F1 F0
 EF EE ED EC EB EA E9 E8 E7 E6 E5 E4 E3 E2 E1 E0
 DF DE DD DC DB DA D9 D8 D7 D6 D5 D4 D3 D2 D1 D0
 CF CE CD CC CB CA C9 C8 C7 C6 C5 C4 C3 C2 C1 C0
 BF BC BB BA B9 AF AA A8 A6 A4 9A 98 95 93 91 8F
 81 7D 7B 79 77 75 6B 69 67 65 5B 57 53 51 41 B8
 B7 B6 B5 B4 B3 AF AA A8 A6 A4 9A 98 95 93 91 8F
 81 7D 7B 79 77 75 6B 69 67 65 5B 57 53 51 41 40
 3F 3E 3D 3C 3B 3A 39 38 37 36 35 34 33 32 31 30
 2F 2E 2D 2C 2B 2A 29 28 27 26 25 24 23 22 21 20
 1F 1E 1D 1C 1B 1A 19 18 17 16 15 14 13 12 11 10
 0F 0E 0D 0C 0B 0A 09 08 07 06 05 04 03 02 01 00
</map>
</collation>

</charset>
. . .

Restart mysqld.

Confirm you have the new collation:

mysql> SHOW COLLATION LIKE 'latin1%';
+---------------------+---------+-----+---------+----------+---------+
| Collation           | Charset | Id  | Default | Compiled | Sortlen |
+---------------------+---------+-----+---------+----------+---------+
. . .
| latin1_general_ci   | latin1  |  48 |         | Yes      |       1 |
| latin1_general_cs   | latin1  |  49 |         | Yes      |       1 |
| latin1_spanish_ci   | latin1  |  94 |         | Yes      |       1 |
| latin1_reverse_ci   | latin1  | 251 |         |          |       0 |
. . .

Create a table and fill it with some text:

mysql> CREATE TABLE foo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    t VARCHAR(60) COLLATE latin1_reverse_ci, 
    x DATE,
    KEY(t)
);

mysql> INSERT INTO foo (t) VALUES ('Harry'), ('Ron'), ('Hermione');

Demonstrate that the sort order is the reverse of what you'd think it should be:

mysql> SELECT * FROM foo ORDER BY t ASC;
+----+----------+------+
| id | t        | x    |
+----+----------+------+
|  2 | Ron      | NULL |
|  3 | Hermione | NULL |
|  1 | Harry    | NULL |
+----+----------+------+

Confirm that it doesn't use a filesort:

mysql> EXPLAIN SELECT * FROM foo WHERE t LIKE 'H%' ORDER BY t\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: foo
   partitions: NULL
         type: range
possible_keys: t
          key: t
      key_len: 63
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using index condition

I had to put a condition on the rows, because otherwise it thought it was going to read all the rows in the table, and it did a table-scan and filesort.

Re comment from @ypercube:

It doesn't reverse the order of characters in a given string like REVERSE(), it reverses which characters are considered greater than or less than each other when comparing two strings. So no, the CHAR padding spaces don't matter, they're still at the end of both strings.

Example (no index this time, just using order by):

mysql> create table t (c char(20) collate latin1_reverse_ci);
mysql> insert into t values ('A'), ('B'), ('C');
mysql> select * from t order by c;
+------+
| c    |
+------+
| C    |
| B    |
| A    |
+------+

Edit: no, @ypercube is correct, this doesn't work with CHAR because the space character is in the wrong place when I naively reorder the collation simply by putting all the bytes in the reverse order. That's not the proper way to design a collation.

So this answer should serve as a demo of the mechanics of creating a collation, not as a proper collation for reversing alphabetical order.

Best Answer

Related Solutions

Mysql – Is index like key(timetime) usable when we query: SELECT * … ORDER BY timetime DESC

MySQL – Using Index for Both ASC and DESC on String Column

Related Question