Slow Query Issue (Version 3.22.8+200309)

11 months 3 days ago - 11 months 3 days ago #195900 by rbaier
We observed that LS frequently performs such queries:

select '' as act,
sq.title as sqtitle,
sq.question as sqquestion
from lime_survey_url_parameters up
left join lime_questions q on q.qid=up.targetqid
left join lime_questions sq on q.qid=up.targetsqid
where up.sid='123456';

Each such query takes about 1 second in our mySQL database.

The strange thing about this query is that there are two joins to the same table "lime_questions" for "lime_survey_url_parameters", and that the column "targetsqid" in this table always seems to contain the value NULL.
If this is true, the second join can be omitted, which speeds up the query considerably.

I would be happy to hear your opinions on this.

Last edit: 11 months 3 days ago by rbaier. Reason: error correction

Please Log in to join the conversation.

LimeSurvey Partners
11 months 3 days ago #195934 by c_schmitz

The second part of the query is unfortunately necessary to get the details for any subquestions in case you want to save URL params in subquestions

I don't really understand though, why it would be slow for you as it just compares two indexed IDs. Either you have a huge database and/or you have a very bad SQL server machine.

Best regards

Carsten Schmitz
LimeSurvey project leader

Please Log in to join the conversation.

10 months 4 weeks ago #196052 by rbaier
Thanks for the answer!

The SELECT-command needs a lot of time. Maybe it is simply because the table "lime_questions" in our case contains more than 185000 records.
The table field "lime_questions.qid" is indexed, but "lime_survey_url_parameters.targetqid" and "lime_survey_url_parameters.targetsqid" is not.
The use of a query cache in mySQl speeds up the said query considerably (after it has been stored in the cache).
I still believe that the query can be rewritten to make it faster.

Kind regards
Roland Baier

Please Log in to join the conversation.

Start now!

Just create your account and start using Limesurvey today.

Register now