View Issue Details

Jump to NotesJump to History
This bug affects 1 person(s).
 4
IDProjectCategoryView StatusDate SubmittedLast Update
05585Bug reportsSurvey participants (Tokens)public2011-11-17 08:442012-01-30 18:41
Reporterteddyber Assigned Tomagiclko 
PrioritynormalSeverityminor 
Status closedResolutionfixed 
Product Version1.91+ 
Fixed in Version1.92RC3 
Summary05585: Token export blocks database
Description

When trying to export tokens with the filter "not started" or "started but not yet completed" on surveys with many participants, this query takes up all of the database's resources. Which is kind of unhandy since it blocks users who are answering the questionnaire at this point of time.

Steps To Reproduce
  • survey with many tokens
  • go to Token Management > CSV-Export
  • select token status "not started"
  • click export tokens
  • SHOW PROCESSLIST; in MySQL: -> update queries to survey-table are locked until export query is finished.
Additional Information

currently, the query has the form:
SELECT * FROM tokens<id>
WHERE 1=1 AND token NOT IN (SELECT token FROM survey<id>)

This could be solved by optimizing the query as such:

  • add an index to survey_<id>.token
  • rewrite queries to make a join instead of an in-statement

not started:
SELECT a.* FROM tokens<id> AS a
LEFT OUTER JOIN survey<id> AS b ON a.token=b.token
WHERE b.token IS NULL

started but not yet finished:
SELECT a.* FROM tokens<id> AS a
INNER JOIN survey<id> AS b ON a.token=b.token
WHERE a.completed LIKE 'N'

TagsNo tags attached.
Bug heat4
Complete LimeSurvey version number (& build)11232
I will donate to the project if issue is resolvedNo
BrowserFirefox
Database type & versionMySQL 5.1.52
Server OS (if known)CentOS 6
Webserver software & version (if known)Apache/2.2.15
PHP Version5.3.2

Users monitoring this issue

There are no users monitoring this issue.

Activities

magiclko

magiclko

2012-01-24 20:36

reporter   ~16951

Last edited: 2012-01-24 20:56

Fixed in rev 12184 in _yii and 12186 in _dev.
c_schmitz

c_schmitz

2012-01-30 18:41

administrator   ~17067

1.92RC3 released

Issue History

Date Modified Username Field Change
2011-11-17 08:44 teddyber New Issue
2011-12-29 19:06 c_schmitz Assigned To => c_schmitz
2011-12-29 19:06 c_schmitz Status new => assigned
2012-01-23 17:35 c_schmitz Assigned To c_schmitz => magiclko
2012-01-24 20:36 magiclko Note Added: 16951
2012-01-24 20:36 magiclko Status assigned => resolved
2012-01-24 20:36 magiclko Resolution open => fixed
2012-01-24 20:56 magiclko Note Edited: 16951
2012-01-24 21:45 c_schmitz Fixed in Version => 1.92RC3
2012-01-30 18:41 c_schmitz Note Added: 17067
2012-01-30 18:41 c_schmitz Status resolved => closed
2016-12-08 10:39 c_schmitz Category Tokens => Survey participants (Tokens)