asdas wrote:
> It could be a known issue, but thought it's a well known & well used
> feature so, asking you all.
>
> I am trying to create an index with the following command -
>
> create index an_idx on atable (acol desc, bcol, ccol)
>
> Then when I do a -
>
> show index from atable
>
> It shows for all indexed columns the 'Collation' is 'A'. And also when we
> do a query without any ORDER BY, the result is not sorted in descending
> order on acol. For example a query like
>
> select pk, acol, bcol, ccol from atable
>
> doesn't show the result in descending order of acol, instead it's in
> ascending order of acol.
>
> Is there a simple trick I am missing ?
>
> I am using MySQL version 5.0.3.
>
> Thanks in advance.
Hassan Schroeder wrote:
Note the last line of this excerpt from The Fine Manual :-)
<http://dev.mysql.com/doc/mysql/en/create-index.html>
An index_col_name specification can end with ASC or DESC. These keywords
are allowed for future extensions for specifying ascending or descending
index value storage. Currently they are parsed but ignored; index values
are always stored in ascending order.
HTH!
Also, you cannot expect your results to be ordered wihout an explicit ORDER BY
clause. Without ORDER BY, mysql is free to return rows in any order it
chooses (generally in the order they are found). For example:
CREATE TABLE atable (pk INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
acol CHAR(3), bcol INT, ccol CHAR(3),
INDEX an_idx (acol, bcol, ccol)
);
INSERT INTO atable (acol, bcol, ccol)
VALUES ('aaa',1,'aaa'), ('bbb',1,'aaa'), ('aaa',3,'aaa'),
('aaa',2,'abc'), ('aaa',1,'bbb'), ('bbb',3,'abc'),
('aaa',2,'aaa'), ('aaa',1,'abc'), ('abc',3,'def'),
('bbb',2,'aaa');
DELETE FROM atable WHERE bcol = 3;
INSERT INTO atable (acol, bcol, ccol)
VALUES ('ccc',1,'aaa'), ('ccc',2,'aaa'), ('ccc',1,'bbb');
SELECT pk, acol, bcol, ccol FROM atable;
+----+------+------+------+
| pk | acol | bcol | ccol |
+----+------+------+------+
| 1 | aaa | 1 | aaa |
| 2 | bbb | 1 | aaa |
| 13 | ccc | 1 | bbb |
| 4 | aaa | 2 | abc |
| 5 | aaa | 1 | bbb |
| 12 | ccc | 2 | aaa |
| 7 | aaa | 2 | aaa |
| 8 | aaa | 1 | abc |
| 11 | ccc | 1 | aaa |
| 10 | bbb | 2 | aaa |
+----+------+------+------+
10 rows in set (0.00 sec)
You see? Without ORDER BY, the rows are returned as they are found. Note
that rows from the second INSERT are where the deleted rows were.
An exception may occur if you only ask for columns which are covered by a
single index. In that case, mysql may choose to read the data from the
covering index, rather than from the table. Then you'd get results in index
order:
mysql> SELECT pk FROM atable;
+----+
| pk |
+----+
| 1 |
| 2 |
| 4 |
| 5 |
| 7 |
| 8 |
| 10 |
| 11 |
| 12 |
| 13 |
+----+
10 rows in set (0.00 sec)
mysql> SELECT acol, bcol, ccol FROM atable;
+------+------+------+
| acol | bcol | ccol |
+------+------+------+
| aaa | 1 | aaa |
| aaa | 1 | abc |
| aaa | 1 | bbb |
| aaa | 2 | aaa |
| aaa | 2 | abc |
| bbb | 1 | aaa |
| bbb | 2 | aaa |
| ccc | 1 | aaa |
| ccc | 1 | bbb |
| ccc | 2 | aaa |
+------+------+------+
10 rows in set (0.00 sec)
I wouldn't count on this though. If you need ordered results, you need ORDER
BY.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
