I initially thought you were on to something here. Working assumption was along the lines that perhaps the buffer pool wasn't immediately flushed as it requires "some work" to do so and why bother until the memory was required. But...
Your test is flawed.
What you're seeing in the buffer pool is the pages read as a result of re-attaching the database, not the remains of the previous instance of the database.
And we can see that the buffer pool was not totally blown away by the
detach/attach. Seems like my buddy was wrong. Does anyone disagree or
have a better argument?
Yes. You're interpreting physical reads 0 as meaning there were not any physical reads
Table 'DatabaseLog'. Scan count 1, logical reads 782, physical reads
0, read-ahead reads 768, lob logical reads 94, lob physical reads 4,
lob read-ahead reads 24.
As described on Craig Freedman's blog the sequential read ahead mechanism tries to ensure that pages are in memory before they're requested by the query processor, which is why you see zero or a lower than expected physical read count reported.
When SQL Server performs a sequential scan of a large table, the
storage engine initiates the read ahead mechanism to ensure that pages
are in memory and ready to scan before they are needed by the query
processor. The read ahead mechanism tries to stay 500 pages ahead of
the scan.
None of the pages required to satisfy your query were in memory until read-ahead put them there.
As to why online/offline results in a different buffer pool profile warrants a little more idle investigation. @MarkSRasmussen might be able to help us out with that next time he visits.
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
Try this code that uses a combination of
SUBSTRING,PATINDEX,LEN, andREVERSE, to account for the possibility of 10-13 digit ISBN variability to return the exact ISBN with no trailing extras:...or perhaps differentiating between other numbers in the string from ones that are at least 10 digits in size, like maybe a date of 1987 included in the description (if this is even needed):
i.e.