4

I know a trick to make order by queries faster when we want to order by asc and desc.
For integers store a negated value.
So if I store -7,-11,-8,-5,-1,-2 the order by asc will give -11, -8, -7, 5, -2, -1 and so the actual row will be sorted desc by that column (if e.g I show the non-negated version).
Question: How do I use this trick with strings?
How is a string "negated" or reversed so that the trick works for string columns as well?

Update
MySQL can not use an index for a query of the type:
ORDER BY col1 ASC col2 DESC
If the column I want to descent is an integer I can store the negated value and do the query as:
ORDER BY col1 ASC col2 ASC
In this case the index can be used and I will in fact get the value in descending order due to negation -11, -8, -7, 5, -2, -1 is ascending but if you remove the negation is it descending.
My problem is that I am not clear how I could apply this approach to a string column. Can I somehow get the arithmetical value of the string?

  • 1
    You need to show us what you mean by "negated". ie. does "hello" become "olleh" or something else? This really depends on what you are attempting to accomplish with an ORDER BY ... DESC on a string. – Max Vernon Sep 24 '14 at 20:32
  • 1
    Why would you ever want to do that? Why not use ORDER BY column DESC? Frankly, this is the worst idea I've seen today. – ypercubeᵀᴹ Sep 24 '14 at 20:34
  • 1
    OK. Makes some sense now. Are both columns VARCHAR? – ypercubeᵀᴹ Sep 24 '14 at 20:44
  • 2
    Yes. If both are varchar, I can't think of any way to do this. If the first is integer, you can do your "trick" on that one, and then use ORDER BY col1 DESC, col2 DESC – ypercubeᵀᴹ Sep 24 '14 at 20:47
  • 1
    No, lexicographic ordering is more complicated than a number ordering. Then there are collations and case sensitivy that complicate things even more. I suggest Postgres, where you can define order (ASC, DEC) on indexes. – ypercubeᵀᴹ Sep 24 '14 at 20:53
4

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.

  1. 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>
    
  2. 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>
    . . .
    
  3. Restart mysqld.

  4. 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 |
    . . .
    
  5. 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');
    
  6. 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 |
    +----+----------+------+
    
  7. 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.

  • But in MySQL the index can only be used if the columns are either all ASC or all DESC and not a mix. – Cratylus Sep 24 '14 at 20:57
  • I did not understand the last paragraph.Is the custom collation like a comparator?How will it help using the index? – Cratylus Sep 24 '14 at 21:05
  • The mapping in latin1.xml is it from the hex codes from here? Because I think some are missing? ic.unicamp.br/~stolfi/EXPORT/www/ISO-8859-1-Encoding.html – Cratylus Sep 25 '14 at 19:17
  • @Cratylus, I just copied the block of codes from the latin1_general_ci collation in the same file, and reversed the order. – Bill Karwin Sep 25 '14 at 19:24
  • This is a very interesting approach.I think it is "hacky" (isn't it?) and requires a server restart but it does the job.I wasn't expected that this was the only way – Cratylus Sep 25 '14 at 19:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.