- Posts: 15
- Thank you received: 0
Slow Query Issue (Version 3.22.8+200309)
11 months 3 days ago - 11 months 3 days ago #195900
by rbaier
Slow Query Issue (Version 3.22.8+200309) was created by rbaier
We observed that LS frequently performs such queries:
select '' as act,
up.*,
q.title,
sq.title as sqtitle,
q.question,
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.
Roland
select '' as act,
up.*,
q.title,
sq.title as sqtitle,
q.question,
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.
Roland
Last edit: 11 months 3 days ago by rbaier. Reason: error correction
Please Log in to join the conversation.
11 months 3 days ago #195934
by c_schmitz
Best regards
Carsten Schmitz
LimeSurvey project leader
Replied by c_schmitz on topic Slow Query Issue (Version 3.22.8+200309)
Hello,
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.
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
Replied by rbaier on topic Slow Query Issue (Version 3.22.8+200309)
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
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.
