The question lookup query in the Textcaptcha module results in MySQL temp tables and file sorts, which causes degraded performance, especially when the questions table is large.

The query can be optimized... patch following.

Comments

bryanbraun’s picture

Assigned: bryanbraun » Unassigned
Status: Active » Needs review
StatusFileSize
new2.41 KB

This 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.

traviscarden’s picture

Status: Needs review » Reviewed & tested by the community

I 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.

$before = microtime(TRUE);
for ($i = 0; $i < 10000; $i++) {
  textcaptcha_get_random_question();
}
$after = microtime(TRUE);
$elapsed = $after - $before;
dpm($elapsed, 'Elapsed');

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.

bryanhirsch’s picture

Thanks everyone! Merged and released with 6.x-1.3 and 7.x-1.4.

bryanhirsch’s picture

Status: Reviewed & tested by the community » Fixed

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.