Joomla is a great platform for building web sites and RSForm is a great plugin for Joomla to handle form submissions—but with a recent project we found that RSForms was having some serious trouble with only a moderate amount of data (2,000 submissions).
I tried to find the source of the problem and always got to the JOIN statement where it would routinely take 10+ seconds to run one query—add in multiple queries and filters and it would take over a minute just to get 500 or so records from MySQL.
I almost gave up hope of finding a solution and resolved to rewrite the entire search & display module, but then I decided to check the indexes—an amateur mistake not looking at this sooner.
Sure enough, no indexes on the fields we join the tables on.
To fix the problem add the following indexes (remember, we’re not talking about Primary Keys or Unique indexes here):
on jos_rsform_submissions (table name may be different) add an index to the FormId field.
on jos_rsform_submission_values (table name may also be different) add an index to the SubmissionId field.
or in SQL:
ALTER TABLE `jos_rsform_submissions` ADD INDEX ( `FormId` ) ALTER TABLE `jos_rsform_submission_values` ADD INDEX ( `SubmissionId` )
Those queries which were taking 1 minute to run now take roughly 0.5 seconds. The queries are really inefficient but I can live with requests under a second.
Now I can get back to enjoying the Joomla community—they really are awesome!