Optimize / faster MYSQL query speed

Let's say you have 5000 records on database, it will take more than 15 seconds to select a record from 5000 records if you didn't optimize the query, because it loop through all 5000 records to search for a particular record for you. Actually you can optimize / faster the query to select the record within a second, perhaps 0.03second. There are many issues that causing slow query and there are many solutions to optimize / faster the query. I will list out all the issues and solutions here :
1 - Set index to the field that in WHERE clause
Example query : SELECT id FROM `messageslive` WHERE username='zac1985';
You need to set index to the field of username, please refer to the picture below :

For testing purpose, please follow the steps :
a) Click SQL on top menu.
b) Type in the mysql query, eg SELECT id FROM `messageslive` WHERE username='zac1985';
c) Click Explain SQL.
d) check the value of "Row" field. (Let's say you have 5000 rows of messages, but there are only 100 messages are posted by zac1987. If you didn't set index to the field that in WHERE clause, value of "Row" should be 5000, it mean query loop through all 5000 records. If you have set index to WHERE clause field, value of "Row" should be 100 only.

2 - Use subquery for RAND() * Max()
Example query : SELECT CEIL(RAND() * MAX(id)) FROM messageslive;
Although you have set index to the id field, RAND() * MAX(id) will generate an id which is not indexed, so causing lost optimization. We can use the solution of subquery like : SELECT RAND() * (SELECT MAX(id) FROM random);

3 - Replace WHERE clause with JOIN clause if there is a subquery in WHERE clause
Example query : SELECT name FROM random WHERE id = (SELECT CEIL(RAND() * (SELECT MAX(id) FROM random)));
We can use the solution of replacing WHERE clause with JOIN clause. JOIN clause consist of "AS" and "USING", eg : SELECT name FROM random JOIN (SELECT CEIL(RAND() * (SELECT MAX(id) FROM random)) AS id) AS r2 USING (id);

More at Top 10 SQL Performance Tips and 10 Tips for Optimizing MySQL Queries (That don’t suck).