[
https://issues.apache.org/jira/browse/CONNECTORS-584?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13528529#comment-13528529
]
Karl Wright commented on CONNECTORS-584:
----------------------------------------
It also seems to be the case that for MySQL 5.5 the table names and database
names in the MySQL internal tables are in lower case, and we're querying for
upper case. So we do not find tables and indexes as we should, and thus get
errors on startup on MySQL. This too will be fixed as part of this ticket.
> Crawling with MySQL does not use indexes for order-by on critical queries
> -------------------------------------------------------------------------
>
> Key: CONNECTORS-584
> URL: https://issues.apache.org/jira/browse/CONNECTORS-584
> Project: ManifoldCF
> Issue Type: Bug
> Components: Framework core
> Affects Versions: ManifoldCF 1.0.1
> Reporter: Karl Wright
> Assignee: Karl Wright
> Fix For: ManifoldCF 1.1
>
>
> The following ORDER-BY query is taking a long time on MySQL:
> {code}
> --------------------------------------------------------
> # Time: 121204 16:25:40
> # User@Host: manifoldcf[manifoldcf] @ localhost [127.0.0.1]
> # Query_time: 7.240532 Lock_time: 0.000204 Rows_sent: 1200 Rows_examined:
> 611091
> SET timestamp=1354605940;
> SELECT
> t0.id,t0.jobid,t0.dochash,t0.docid,t0.status,t0.failtime,t0.failcount,t0.priorityset
> FROM jobqueue t0 WHERE t0.status IN ('P','G') AND t0.checkaction='R' AND
> t0.checktime<=1354605932817 AND EXISTS(SELECT 'x' FROM jobs t1 WHERE
> t1.status IN ('A','a') AND t1.id=t0.jobid AND t1.priority=5) AND NOT
> EXISTS(SELECT 'x' FROM jobqueue t2 WHERE t2.dochash=t0.dochash AND t2.status
> IN ('A','F','a','f','D','d') AND t2.jobid!=t0.jobid) AND NOT EXISTS(SELECT
> 'x' FROM prereqevents t3,events t4 WHERE t0.id=t3.owner AND
> t3.eventname=t4.name) ORDER BY t0.docpriority ASC,t0.status
> ASC,t0.checkaction ASC,t0.checktime ASC LIMIT 1200;
> # Time: 121204 16:25:44
> # User@Host: manifoldcf[manifoldcf] @ localhost [127.0.0.1]
> # Query_time: 3.064339 Lock_time: 0.000084 Rows_sent: 1 Rows_examined:
> 406359
> SET timestamp=1354605944;
> SELECT docpriority,jobid,dochash,docid FROM jobqueue t0 WHERE status IN
> ('P','G') AND checkaction='R' AND checktime<=1354605932817 AND EXISTS(SELECT
> 'x' FROM jobs t1 WHERE t1.status IN ('A','a') AND t1.id=t0.jobid) ORDER BY
> docpriority ASC,status ASC,checkaction ASC,checktime ASC LIMIT 1;
> -------------------------------------------------------
> {code}
> I wonder if the queries appropriately use index of the table.
> As a result of EXPLAIN against the slow query, there was filesort.
> There seems to be some conditions that MySQL does not use index depending on
> ORDER BY:
> - Executing ORDER BY against multiple keys
> - When keys selected from records are different from keys used by ORDER BY
> Since filesort was happening, fully scanning records should be having MCF
> slower.
> Do you think this could happen even in PostgreSQL or HSQLDB?
> Do you think queries could be modified to use index appropriately?
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
