MySQL Forums
Forum List  »  General

Re: tables with 3,500,000 rows, indexed but still v slow
Posted by: Adam Hardy
Date: June 18, 2010 06:00PM

Yes, that's right. The TRADE and the DOLLAR_RETURN table have millions of records already - around 3 or 4M.

That SELECT is executed ~7K times to discover whether TRADE has any children. Then the new child records are done - INSERT INTO DOLLAR_RETURN - the same amount. During this transaction, there are no inserts into TRADE - that's another earlier transaction in the user interaction that is also slow, but not this slow.

Then the same number of UPDATEs on TRADE are done - this is the versioning of the ORM layer. I haven't got that far in my talking to the ORM guys yet but I don't see why having new child records should change the version of the parent. Maybe it's configurable in the ORM config.

It doesn't change any primary keys.

So this is the meat of the transaction and is repeated almost 7K times:

SELECT [snipped]
    FROM DOLLAR_RETURN t0 INNER JOIN MARKET_SYSTEM t1 ON 
        t0.MARKET_SYSTEM_ID = t1.ID INNER JOIN TEST_RUN t2 ON 
        t1.TEST_RUN_ID = t2.ID LEFT OUTER JOIN MARKET t3 ON 
        t2.MARKET_ID = t3.ID INNER JOIN REPO t6 ON t2.REPO_ID = t6.ID 
        LEFT OUTER JOIN EXCHANGE t4 ON t3.EXCHANGE_ID = t4.ID LEFT 
        OUTER JOIN ACCOUNT t5 ON t4.ACCOUNT_ID = t5.ID 
    WHERE t0.TRADE_ID = ? 
[params=(long) 4388646]

INSERT INTO DOLLAR_RETURN (ID, MARKET_SYSTEM_ID, TRADE_ID, PROFIT, 
        OWNER_ID, VERSION, CREATED, MODIFIED) 
    VALUES (?, ?, ?, ?, ?, ?, ?, ?) 
[params=(long) 4367652, (long) 11450, (long) 4383130, (BigDecimal) 0.000910000000, (long) 1, (int) 1, (Timestamp) 2010-06-15 11:45:29.443, (Timestamp) 2010-06-15 11:47:19.084]


UPDATE TRADE 
    SET VERSION = ?, MODIFIED = ? 
    WHERE ID = ? AND VERSION = ? 
[params=(int) 2, (Timestamp) 2010-06-15 11:47:19.792, (long) 4387569, (int) 1]

So that's about 15 hits, twice your guess!

I'm not sure about the 'unnecessary row' count that is scanned. Do you mean the number of other rows in the other tables? TRADE and DOLLAR_RETURN have got millions of records, but the only ones accessed here should be all bunched at the bottom of the table (at least for TRADE because they were generally all inserted by the user 5 mins before this operation.)

Thanks v much



Edited 1 time(s). Last edit at 06/18/2010 06:04PM by Adam Hardy.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.