homepage Welcome to WebmasterWorld Guest from 54.196.206.80
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Accredited PayPal World Seller

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
order by RAND() . need a faster solution
nickCR




msg:4135659
 2:26 am on May 20, 2010 (gmt 0)

Hello All,

So here is a query i'm trying to run to get a randomized result from our db.

I've been looking for hours now on a solution and just don't seem to be finding what I need.

The difference is as such.

When I run the query without the order by rand() limit 1 I get a result in 0.0052

When I run the query with the above I get a result in 0.0517

It may seem like a decent result time but when the entire page i'm trying to load this query onto is loading in 0.03 that is adding quite a bit of extra latency (0.07 / 0.08).

Here is the query:
SELECT t3.idS, sum(t1.value - t1.price) as savings
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.idC
INNER JOIN table3 t3 ON t3.idC = t1.id
WHERE t3.idS NOT IN (5653,21740)
GROUP BY t3.id
HAVING sum(t1.value - t1.price) > 100
ORDER BY RAND()
LIMIT 1


Any ideas what I can do to avoid the extra result time?

table1 = 598
table2 = 598
table3 = 10285

BTW if I remove table2 from query it takes almost double the time.. I have no idea why since it's not even being used in any of the values.

I would truely appreciate any help.

Thanks in advance,

Nick

 

zergtant




msg:4135683
 3:20 am on May 20, 2010 (gmt 0)

try this one use min and max with the Primary Key id
SELECT *
FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`))+(SELECT MIN(id) FROM `table`)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id LIMIT 1;

and btw i say you use multiply join and GROUP BY, may be you can create index to make select faster

nickCR




msg:4136012
 4:57 pm on May 20, 2010 (gmt 0)

Thanks for your suggestion.

However after spending hours and hour last night trying to find a faster way to do this I think it actually makes more sense to run a process once a day after we run our financial batch to re-calculate the "savings" and add it to the members table and then do a simple select following some of the solutions that already exist out there.

serutan




msg:4139775
 7:17 am on May 26, 2010 (gmt 0)

How about this: generate a random string which you pass to your procedure as a parameter, and order your results by this string bitwise ANDed with some string field in your data. Something tells me that would run faster than having mysql generate a random number for every row, but I really dunno.

The main question that springs to mind is, I know efficiency is a good thing but will eliminating a few hundredths of a second of processing time in this particular process pay you back for your efforts?

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved