EPrints Technical Mailing List Archive

Message: #06401

< Previous (by date) | Next (by date) > | < Previous (in thread) | Next (in thread) > | Messages - Most Recent First | Threads - Most Recent First

[EP-tech] Long Query Lock

​​Hi All,

 

We have a problem with slow database connection due to long query. Something it locks out the system...my finding the query is something like below. I think it is indexing. We did customized the repository with new fields, quite a lot ...

 

How to handle this problem? When this query is in the connection, it will slow down eprints and sometimes, I will need to kill this process.


Hope to get advise from everybody who might faced the same problem. 


Thank you in advance for your help. Have a nice weekend.


Zemy Azmira

IIUM Library, Malaysia

 

          INSERT INTO `cache15972759`(`pos`, `eprintid`) SELECT @i:=@i+1, `eprintid` FROM (SELECT `eprint`.`eprintid` FROM `eprint` LEFT JOIN `eprint__ordervalues_en` ON `eprint`.`eprintid`=`eprint__ordervalues_en`.`eprintid`, (SELECT `eprint`.`eprintid` AS `eprintid` FROM `eprint`, `eprint__rindex` AS `eprint__rindex` WHERE `eprint`.`eprintid`=`eprint__rindex`.`eprintid` AND `eprint__rindex`.`field`='title' AND `eprint__rindex`.`word`='evaluation') AS `and_139772665284464_0`, (SELECT `eprint`.`eprintid` AS `eprintid` FROM `eprint`, `eprint__rindex` AS `eprint__rindex` WHERE `eprint`.`eprintid`=`eprint__rindex`.`eprintid` AND `eprint__rindex`.`field`='title' AND `eprint__rindex`.`word`='effect') AS `and_139772665284464_1`, (SELECT `eprint`.`eprintid` AS `eprintid` FROM `eprint`, `eprint__rindex` AS `eprint__rindex` WHERE `eprint`.`eprintid`=`eprint__rindex`.`eprintid` AND `eprint__rindex`.`field`='title' AND `eprint__rindex`.`word`='copper') AS `and_139772665284464_2`, (SELECT `eprint`.`eprintid` AS `eprintid` FROM `eprint`, `eprint__rindex` AS `eprint__rindex` WHERE `eprint`.`eprintid`=`eprint__rindex`.`eprintid` AND `eprint__rindex`.`field`='title' AND `eprint__rindex`.`word`='electroplating') AS `and_139772665284464_3`, (SELECT `eprint`.`eprintid` AS `eprintid` FROM `eprint`, `eprint__rindex` AS `eprint__rindex` WHERE `eprint`.`eprintid`=`eprint__rindex`.`eprintid` AND `eprint__rindex`.`field`='title' AND `eprint__rindex`.`word`='parameter') AS `and_139772665284464_4`, (SELECT `eprint`.`eprintid` AS `eprintid` FROM `eprint`, `eprint__rindex` AS `eprint__rindex` WHERE `eprint`.`eprintid`=`eprint__rindex`.`eprintid` AND `eprint__rindex`.`field`='title' AND `eprint__rindex`.`word`='adhesion') AS `and_139772665284464_5`, (SELECT `eprint`.`eprintid` AS `eprintid` FROM `eprint`, `eprint__rindex` AS `eprint__rindex` WHERE `eprint`.`eprintid`=`eprint__rindex`.`eprintid` AND `eprint__rindex`.`field`='title' AND `eprint__rindex`.`word`='using') AS `and_139772665284464_6`, (SELECT `eprint`.`eprintid` AS `eprintid` FROM `eprint`, `eprint__rindex` AS `eprint__rindex` WHERE `eprint`.`eprintid`=`eprint__rindex`.`eprintid` AND `eprint__rindex`.`field`='title' AND `eprint__rindex`.`word`='response') AS `and_139772665284464_7`, (SELECT `eprint`.`eprintid` AS `eprintid` FROM `eprint`, `eprint__rindex` AS `eprint__rindex` WHERE `eprint`.`eprintid`=`eprint__rindex`.`eprintid` AND `eprint__rindex`.`field`='title' AND `eprint__rindex`.`word`='surface') AS `and_139772665284464_8`, (SELECT `eprint`.`eprintid` AS `eprintid` FROM `eprint`, `eprint__rindex` AS `eprint__rindex` WHERE `eprint`.`eprintid`=`eprint__rindex`.`eprintid` AND `eprint__rindex`.`field`='title' AND `eprint__rindex`.`word`='methodology') AS `and_139772665284464_9` WHERE `eprint`.`eprintid`=`and_139772665284464_0`.`eprintid` AND `eprint`.`eprintid`=`and_139772665284464_1`.`eprintid` AND `eprint`.`eprintid`=`and_139772665284464_2`.`eprintid` AND `eprint`.`eprintid`=`and_139772665284464_3`.`eprintid` AND `eprint`.`eprintid`=`and_139772665284464_4`.`eprintid` AND `eprint`.`eprintid`=`and_139772665284464_5`.`eprintid` AND `eprint`.`eprintid`=`and_139772665284464_6`.`eprintid` AND `eprint`.`eprintid`=`and_139772665284464_7`.`eprintid` AND `eprint`.`eprintid`=`and_139772665284464_8`.`eprintid` AND `eprint`.`eprintid`=`and_139772665284464_9`.`eprintid` AND (`eprint`.`metadata_visibility` = 'show' AND (`eprint`.`eprint_status` = 'buffer' OR `eprint`.`eprint_status` = 'archive')) GROUP BY `eprint`.`eprintid`, `eprint__ordervalues_en`.`date`, `eprint__ordervalues_en`.`creators_name`, `eprint__ordervalues_en`.`title` ORDER BY `eprint__ordervalues_en`.`date` DESC, `eprint__ordervalues_en`.`creators_name` ASC, `eprint__ordervalues_en`.`title` ASC LIMIT 2147483647) `S`

Select all. | Invert selection.