Mysql innodb query returning slow result

Question

I have following query which is taking time. Mytable type is innodb and have primary key on field tsh_id I have also added index on transaction_Id field

following is implementation inside my database stored procedure.

 DECLARE lv_timestamp DATETIME(3);
    SET @lv_timestamp = NOW(3);

    IF(mycondition) then    
        SET @lv_Duration :=( SELECT UNIX_TIMESTAMP (@lv_timestamp)  - UNIX_TIMESTAMP ( `changedon` ) 
        FROM  `MyTable`         
        WHERE transaction_Id = _transaction_Id        
        ORDER BY tsh_id DESC
        LIMIT 1)        
    End if;

Please suggest any sort of improvement

Edit:

Explain to query says

"select_type":"SIMPLE",
"table":"MyTable",
"type":"ref",
"possible_keys":"IX_MyTable_Transaction",
"key":"IX_MyTable_Transaction",
"key_len":"98",
"ref":"const",
"rows":1,
"Extra":"Using where"

I can't see right away from the explain that the query by itself should be slow. Maybe someone else with more mysql optimization experience will have some input. — Joachim Isaksson, Apr 26 '16 at 14:50
Please provide more info on what you mean by "slow" - how long does it take, how many times are you calling it, how many records are in the table, how long do you want it to take, etc. Also, what data type is transaction_id? The "Explain" output says the length is 98, so just curious how that is defined. — Matt Jordan, Apr 26 '16 at 15:00
@Kamran Shahid - try to use a composite index with (transaction_Id, _wfrecord_Id) or (tsh_id , transaction_Id, _wfrecord_Id) . mysql can only use ONE INDEX in a query — Bernd Buffen, Apr 26 '16 at 15:02
_wfrecord_Id is the parameter name for transaction_id field. Let me rename it for any confusion. tsh_id is not used in where clause? what's the advatange of making this primary key a composite key with transaction_id which already has it's own index — Kamran Shahid, Apr 26 '16 at 15:04
No @BerndBuffen , the only useful index is `(transaction_id, tsh_id)` -- start with the `=`, then add on the `ORDER BY`. A slight improvement would be the 'covering index': `(transaction_id, tsh_id, changedon)`. — Rick James, Apr 26 '16 at 21:08
@KamranShahid - It is _sometimes_ advantageous to design the `PRIMARY KEY` for a range scan. The table's records are arranged in `PRIMARY KEY` order, so if you need to scan a table in exactly the PK order, it will run faster than having that same key specified as a "secondary key". In the case of `LIMIT 1`, the difference is quite insignificant. — Rick James, Apr 26 '16 at 21:11

score 0 · Answer 1 · answered Apr 26 '16 at 15:10

0

Make sure you have an index on MyTable.transaction_Id
Make sure you have the innodb_buffer_pool_size set to a decent value in your MySQL config

answered Apr 26 '16 at 15:10

Tudor Constantin

26,330
7
49
72

i have index on the MyTable.transaction_Id and i think innodb_buffer_pool_size is set around 1GB or 500 MB (My mysql server has 4 GB of Ram) – Kamran Shahid Apr 26 '16 at 18:02

score 0 · Answer 2 · answered Apr 26 '16 at 16:17

0

I am fairly certain your primary key is not a clustered key (its might be null or not unique or you changed it at one point), because that would explain this behaviour, at least if transaction_Id isn't unique either (and otherwise you wouldn't need limit 1).

To improve this specific query, you can create the following index:

create index ix_mytable_transaction_id_tsh_id on MyTable (transaction_id, tsh_id desc);

Use explain again.

If it doesn't use the new key, force it:

SELECT ... FROM  `MyTable` force index(ix_mytable_transaction_id_tsh_id) ...

answered Apr 26 '16 at 16:17

Solarflare

10,721
2
18
35

It's a Mysql innodb table. Mysql version is 5.6.27. tsh_id is a primary key with auto-increment. don't know. I have tried the composite index but it is almost redundant in my case as i am only searching on transaction_id. while tsh_id (primary key) is in order by clause – Kamran Shahid Apr 26 '16 at 18:00
Could you post the results of: `show index from MyTable;`, `explain extended `, `show warnings;` (after explain extended), the number of rows in the resultset `SELECT count(*) FROM MyTable WHERE transaction_Id = _transaction_Id`, the time it takes to get that, and the time your query takes. – Solarflare Apr 26 '16 at 20:15

Mysql innodb query returning slow result

2 Answers2