As i have to do an order by, on a column for a table having millions of records, I found an approcah which can do faster sort in the following way.
It is like doing an Inner Join with a result set which is ordered by the interested order by column, where in such column has to be indexed, so that it will use the created Index. So in this case, the time is not utilized at all for Order by and it is only Join time, so the result was faster.
SELECT distinct pd.message_id, pd.receipt_time_stamp
FROM fm_package_db.dla_package_details pd
INNER JOIN
(SELECT message_id FROM fm_package_db.dla_package_details
where receipt_time_stamp between ‘2015-03-30 00:00:00’ AND ‘2015-03-30 23:59:59’
ORDER BY receipt_time_stamp desc) as temp ON temp.message_id = pd.message_id
WHERE pd.Site_Code = ‘SITE_6’ LIMIT 0 , 25
Now the problem is, this approach will do an Order by on any column in both Asc and Desc perfectly in Windows environment of Mysql. But it is always doing an ‘asc’ sort only in Linux environment of Mysql, but not doing Desc sorting.
Is there any such implementation differences with different Mysql instances in Linux and Windows, for the above query to not to work.
Pls help out.