Hello All,
When I use "order by .. DESC" in query, time always more then in
"order by .. ASC" case.
Explain say "Using filesort",in "DESC" case query.
Why???
Why I can't use key value in select like that:
select * from TableName where KEY(KEYNAME)<concat('value1','value2')
or
select * from TableName order by KEY(KEYNAME)
I think in that case query optimizing will be more simple.
example:
CREATE TABLE cSectText (
SectID int(11) NOT NULL default '0',
ID int(11) NOT NULL default '0',
Date date NOT NULL default '0000-00-00',
PRIMARY KEY (ID,SectID),
UNIQUE KEY SectID (SectID,Date,ID)
) TYPE=MyISAM;
mysql> select * from cSectText where SectID=1 order by date desc limit 1;
+--------+-------+------------+
| SectID | ID | Date |
+--------+-------+------------+
| 1 | 35131 | 2001-07-16 |
+--------+-------+------------+
1 row in set (0.15 sec)
mysql> select * from cSectText where SectID=1 order by date limit 1;
+--------+-------+------------+
| SectID | ID | Date |
+--------+-------+------------+
| 1 | 31118 | 1993-02-15 |
+--------+-------+------------+
1 row in set (0.00 sec)
mysql> explain select * from cSectText where SectID=1 order by date desc limit 1;
+-----------+------+---------------+--------+---------+-------+-------+-----------------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
| |
+-----------+------+---------------+--------+---------+-------+-------+-----------------------------------------+
| cSectText | ref | SectID | SectID | 4 | const | 21892 | where used;
|Using index; Using filesort |
+-----------+------+---------------+--------+---------+-------+-------+-----------------------------------------+
1 row in set (0.00 sec)
mysql> explain select * from cSectText where SectID=1 order by date limit 1;
+-----------+------+---------------+--------+---------+-------+-------+-------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
| |
+-----------+------+---------------+--------+---------+-------+-------+-------------------------+
| cSectText | ref | SectID | SectID | 4 | const | 21892 | where used;
|Using index |
+-----------+------+---------------+--------+---------+-------+-------+-------------------------+
1 row in set (0.00 sec)
--
Best regards,
Artem mailto:[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
