Hello everyone,
I'm working with MySQL 3.23.41-log developing a chat application. I need to run a query, order the results in
descending order, then get the last 0-15 entries that are less than 20 minutes old. The query and tables in question
are explained at the bottom of this posting. In the query shown below, the number 1054889629 indicates at 20 minute
old UNIX Timestamp.
The query below returns the correct results, but as of yet, I have been unable to get the query to take advantage of
the indexes in place. When sorting ASC, I can get a reasonable efficient query, however that gives me the oldest 0-15
when I need the newest. If I sort by DESC, which would give me the incorrect answer, the query uses Filesort, which
is unacceptable for my application.
This is a problem that is fixed in MySQL 4. If you can upgrade, that should help you a lot.
http://www.mysql.com/doc/en/News-4.0.0.html
The order the fields are selected in is unimportant and can be rearranged if need be. The WHERE and ORDER BY sections
can be change freely so long as the resultant data is the same and indexes can be added or removed as needed. This is
the only major query being run against the database.
Ideally, I'd like the query to use index, but I'd be satisfied if it just didn't use filesort. Can anyone help me
tighten this up?
Thanks! Jacob
-- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
