Hello,
I have a stored proc that has the following query in it. There are a
couple of million rows in the tables and the query usually takes about 5
seconds to run. Sometimes, the query takes over 25 seconds though. I can't
figure out why. It seams random. If I run the query 10 times and it runs
slow about once. I have tested on different machines with the same result.
I tested the stored proc with same results. That is why I have extracted
the query for this question. I have indexes on ACCOUNTS.ACCOUNT_NUMBER,
ACCOUNTS.GUARANTOR_NAME,ACCOUNTS.ACCOUNT_ID,and STATEMENTS.ID_ACCOUNT. Does
anyone have any idea what is causing this? I would also appreciate any
ideas on optimizing the query. Even some tips on setting the database
parameters such as pagesize would help.
Thank in advance.
The query is below, followed by the results of the explain.
SELECT
ACCOUNTS.ACCOUNT_NUMBER,
ACCOUNTS.GUARANTOR_NAME,
STATEMENTS.DATE_OF_STATEMENT,
STATEMENTS.BALANCE,
STATEMENTS.STATEMENT_ID
FROM
THEDB.ACCOUNTS
INNER JOIN
THEDB.STATEMENTS
ON
ACCOUNTS.ACCOUNT_ID = STATEMENTS.ID_ACCOUNT
WHERE
UCASE(ACCOUNTS.ACCOUNT_NUMBER) LIKE '%%'
AND
UCASE(ACCOUNTS.GUARANTOR_NAME) LIKE '%%'
AND
ACCOUNTS.ID_ITEM = 268
AND
ROWNO < 500
ORDER BY
ACCOUNT_NUMBER
EXPLAIN:
TABLENAME COLUMN_OR_INDEX STRATEGY PAGECOUNT
ACCOUNTS IDX_ACCOUNT_ITEM_ID EQUAL CONDITION FOR INDEX 15475
STATEMENTS IDX_STATEMENTS_ID_ACCOUNT JOIN VIA INDEXED COLUMN 124841
RESULT IS COPIED , COSTVALUE IS 196093
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
