Mysql Query Taken Long Time

Question

My table tbltemp has around 9 lakhs records. The columns are: id (primary key, autoincrement), name, qty, price, status, mod_date, created_date.

My query is:

SELECT * 
 FROM tbltemp 
 WHERE qty > 3 
 ORDER By Rand() 
 LIMIT 50

It takes around 7 to 10 seconds to execute. How can I optimize my table or query?

possible duplicate of [How can i optimize MySQL's ORDER BY RAND() function?](http://stackoverflow.com/questions/1244555/how-can-i-optimize-mysqls-order-by-rand-function) — Interrobang, Jun 01 '12 at 09:59
How long does query `select * from tbltemp where qty > 3 ORDER By id Limit 50` takes? — Rahul, Jun 01 '12 at 10:05
@Interrobang Not quite - the other question limits result to one row, while here are multiple rows. Solution found there is not applicable here. — Nikola Markovinović, Jun 01 '12 at 10:18
You might take a look at [this article](http://jan.kneschke.de/projects/mysql/order-by-rand/). — Nikola Markovinović, Jun 01 '12 at 10:29

score 0 · Answer 1 · answered Jun 01 '12 at 09:59

0

You should create ad index on qty column.
Anyway ORDER By Rand() is time consuming...

answered Jun 01 '12 at 09:59

Marco

56,740
14
129
152

That will be a optional good thing but not needed at all. I am sure if he runs the query `select * from tbltemp where qty > 3 ORDER By id Limit 50` ... will be much faster. – Rahul Jun 01 '12 at 10:07
I want to show the random list when each time user refresh my page..thats why i use the "ORDER BY RAND()" in query @Rahul – Flora Clinton Jun 01 '12 at 11:05

score 0 · Answer 2 · answered Jun 01 '12 at 12:48

Here is an alternative for order by rand(). Try this once and see if it works

select @lims := floor(1+rand()*(count(*) - 1)) from tbltemp;  

PREPARE mystatement FROM "SELECT * FROM tbltemp WHERE qty > 3 LIMIT ?, 50";

EXECUTE mystatement USING @lims;

score 0 · Answer 3 · answered Jun 02 '12 at 09:02

In your query :

SELECT * FROM tbltemp WHERE qty > 3 ORDER By Rand() LIMIT 50

For query optimization please take a look :

ORDER BY clause is the main reason for taking long time.
Add an Index to qty column.
Also the storage engine you are using can also affect the performance optimization of query.MYISAM is the fastest storage engine.

Mysql Query Taken Long Time

3 Answers3