How to improve QUERY

Asked

Viewed 56 times

1

As I could improve this query, it returns me 30 records from a table that contains 43thousand records.

NOTE: I use index in the core column (much improved).

((SELECT id,situation,info FROM request WHERE core=1 ORDER BY RAND() LIMIT 9)
UNION ALL
(SELECT id,situation,info FROM request WHERE core=3 ORDER BY RAND() LIMIT 7)
UNION ALL
(SELECT id,situation,info FROM request WHERE core=5  ORDER BY RAND() LIMIT 5)
UNION ALL
(SELECT id,situation,info FROM request WHERE core=6  ORDER BY RAND() LIMIT 2)
UNION ALL
(SELECT id,situation,info FROM request WHERE core=2 ORDER BY RAND() LIMIT 3)
UNION ALL
(SELECT id,situation,info FROM request WHERE core=4 ORDER BY RAND() LIMIT 4));
php mysql query

  • have you thought about using the ...where core in (3,2,5...) ... Limit 30?

  • Yes, but each core has the limit specific.

  • What is the execution time of this query ?

  • this query that you assembled was not to return only 30 results, because returned the total of 43k ?

  • I think first is to remove this commando from the order by

  • My fault! copy/Paste, Result: (30 total, 2,340 sec)

  • Because you sort randomly ?

  • Felipe, it needs to be random.

  • I need to bring in random records.

  • I understand, but randomizing order by takes a lot of query performance.

  • Exactly!!! Still missing some 20,000 more records to enter...

  • I will answer with the only thing that can think to improve your code, test and ne speaks if improves the result.

  • What can help is to divide this query, instead of one, turns into 6 and loads them asynchronously, so the user reads the first ones while loading the others. Won’t improve performance, but improves user experience

Show 8 more comments

1 answer

2


The only thing I could think of to optimize your code is to remove the rand() and place after the union, because I believe he weighs the most. I believe that executing it only once is better than executing it 6 times. 

(SELECT id,situation,info FROM request WHERE core=1 LIMIT 9)
UNION ALL
(SELECT id,situation,info FROM request WHERE core=3 LIMIT 7)
UNION ALL
(SELECT id,situation,info FROM request WHERE core=5 LIMIT 5)
UNION ALL
(SELECT id,situation,info FROM request WHERE core=6 LIMIT 2)
UNION ALL
(SELECT id,situation,info FROM request WHERE core=2 LIMIT 3)
UNION ALL
(SELECT id,situation,info FROM request WHERE core=4 LIMIT 4)
ORDER BY RAND();

Test and tell me the difference in the result.

  • My first attempt was this way, but the results do not come in the order type: core=1 + 9 records core=2 + 7 records etc... ORDER BY RAND() at the end the records comes out of order

  • So do you want Rand() to be "grouped" by core? for example: the random core 1 values then the random core 3 values and so on ?

  • Yes, that’s exactly it.

  • So you really have to do it the way you’re doing it. What you can do is use this article here and tailor it to your case. https://www.warpconduit.net/2011/03/23/selecting-a-random-record-using-mysql-benchmark-results/

  • Well, I’ll take a look at this article, thank you @Bulfaitelo.

Browser other questions tagged php mysql query

You are not signed in. Login or sign up in order to post.