The thing is I found that mysql takes very long because of the ORDER BY date. So I decide to change it to ORDER by id , which is unique in database. It runs twice faster, but today I figure out that id is set as PRIMARY key, so my question is , if I will change it to UNIQUE , would I get some performance from it ? just because UNIQUE key its more easy to sort ? or what else I can do to increase ORDER by speed ?
How many records are you including in your ORDER? I have tables with several hundred thousand records in them and my queries rarely take more than two seconds to execute. It might not be your query but the number of simultaneous connections to the database, and if you are on a shared host, times that by the number of sites also accessing the database server.
There's no difference in speed between UNIQUE or PRIMARY key. Besides, you should keep it as PRIMARY to enjoy auto-incremented keys. If you're ordering on other columns, you'll need to add indexes to those. You can get an idea of what is wrong by doing EXPLAIN SELECT ... (your query) in phpmyadmin.