I'm looking through the query log performance for quiz and noticing that some of them are surprisingly slow. Surprising until you look at the table defs and see that there's very little use of indices. E.g. quiz_node_relationship, which gets huge, doesn't have any indices.

Adding in indices is pretty easy and has almost no potential for bad side-effects. In my testing so far, adding the proper indices to quiz_choice_answer() dropped a 5 ms query down to 0.6 ms. These do add up.

Another performance problem is suboptimal queries. E.g. the DISTINCT in _quiz_get_questions() slows it down by a factor of two.

Comments

mbutcher’s picture

Yeah, definitely add indices wherever they would help. sdboyer and I started doing that a while back, but there were too many things to do and we never finished. I'm also all for re-writing slow queries and getting rid of DISTINCT where possible.

Keep in mind, though, that the SQL is supposed to be PostGreSQL compliant. As of Quiz 3, things were running fine on pgsql. I'd like to keep it that way.

falcon’s picture

Status: Active » Needs work
falcon’s picture

Status: Needs work » Fixed

I hope this has been significantly improved by now. Please open new issues on specific slow queries/pages if you find any.

turadg’s picture

Nice job on performance, Falcon.

If anyone's using PostgresQL, please let us know whether the current dev snapshot is working okay there.

falcon’s picture

Status: Fixed » Closed (fixed)