Closed (fixed)
Project:
Text CAPTCHA
Version:
7.x-1.x-dev
Component:
Code
Priority:
Normal
Category:
Task
Assigned:
Unassigned
Reporter:
Created:
3 Jun 2014 at 18:15 UTC
Updated:
8 Dec 2014 at 18:34 UTC
Jump to comment: Most recent, Most recent file
Comments
Comment #1
bryanbraun commentedThis removes the Rand() function from our query, which eliminates the temp table and the file sort operations. Instead we count the rows in the table, use PHP to pull a random number from it, and do a simple SELECT instead.
By caching the count, we can ensure that query only runs when the count is updated, instead of on every Textcaptcha page load.
Comment #2
traviscarden commentedI have reviewed and tested this patch. It preserves functionality and improves performance tremendously. Here's the resulting change to the query with a MySQL backend:
Before:
SELECT q.*, RAND() AS random_field FROM textcaptcha_questions q ORDER BY random_field ASC LIMIT 1 OFFSET 0;After:
SELECT q.* FROM textcaptcha_questions q LIMIT 1 OFFSET 0;The new query eliminates the need for a temp table and filesorting. In testing on my local install it was roughly 495% as performant as the old query (i.e., it did the same work in 80% less time). For those interested, I calculated that like so: I ran the following code via Devel on the old code and the new.
It took the old query 19.757333040237 seconds to run 10,000 times. The new query took only 3.9935119152069 seconds to do the same. That's a reduction of 15.763821125 seconds, leading to the percentages cited above.
Comment #5
bryanhirsch commentedThanks everyone! Merged and released with 6.x-1.3 and 7.x-1.4.
Comment #6
bryanhirsch commented