Can some one tell me Why This is Query are very slow?

Question

Can some one explain why this query with IN clause over 5000 record are too slow?

Table strucuture

    CREATE TABLE IF NOT EXISTS `wp_transactions_log` (
  `sync_sequence` bigint(20) unsigned NOT NULL COMMENT 'the sequence number of the sync process/operation that this transaction belong to ',
  `objectid` varchar(100) NOT NULL COMMENT 'the entity/record id',
  `wp_id` bigint(20) unsigned NOT NULL,
  `table_name` varchar(100) NOT NULL COMMENT 'the target wordpress table name this transaction occured/fail for some reason',
  `logical_table_name` varchar(100) NOT NULL,
  `operation` varchar(20) NOT NULL COMMENT 'inser/update/delete',
  `status` varchar(20) NOT NULL COMMENT 'status of the transaction: success,fail',
  `fail_count` int(10) unsigned NOT NULL COMMENT 'how many this transaction failed',
  `fail_description` text NOT NULL COMMENT 'a description of the failure',
  `createdon` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`sync_sequence`,`objectid`,`table_name`,`operation`,`wp_id`),
  KEY `objectid` (`objectid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

This table contain 5k record.

The query :

SELECT wp_id,objectId FROM wp_transactions_log WHERE `operation` = "insert" AND `wp_id` != 0 AND `status` != "ignore" AND `table_name` ='itg_wpclass_dates' AND objectId IN (... 5k record)

even this query are same:

SELECT wp_id,objectId FROM wp_transactions_log WHERE  objectId IN (5k record)

Note: all the parameters in the IN clauses are themselves the same in the table rows. I mean by slow it takes more than 15 Sec.

possible duplicate of http://stackoverflow.com/questions/4226035/why-does-a-query-slow-down-drastically-if-in-the-where-clause-a-constant-is-repl — Wasi Ahmad, Nov 20 '16 at 19:35
too slow you mean how many seconds? I did a similar test years ago, used 10K in `IN` clause and executed in `0.04` seconds. http://stackoverflow.com/a/7818697/153723 I believe problem is not the table. — Ergec, Nov 20 '16 at 19:46
How you obtain the 5k record .. if you use a subselect please add to your question the related code .. — ScaisEdge, Nov 20 '16 at 19:53
No this query is constructed grammatically, so the query is like this, no sub query are included. — M.Abulsoud, Nov 21 '16 at 08:12

score 2 · Answer 1 · answered Nov 20 '16 at 19:51

2

objectid is not indexed. Composite primary key is indexed only. Add index on objectid and then try.

ALTER TABLE wp_transactions_log ADD INDEX (objectid);

Although if you have huge data, then adding index will lock your metadata, use INPLACE algorithm to do it with minimum lock contention.

Also, before youe select statement, just add Explain and provide us the response. It will be a good metrics to identify issue in your table.

answered Nov 20 '16 at 19:51

Naruto

4,221
1
21
32

I created index on objectid but that's useless. – M.Abulsoud Nov 20 '16 at 20:07
Can you show the result of Explain SELECT wp_id,objectId FROM wp_transactions_log WHERE objectId IN (5k record) – Naruto Nov 20 '16 at 20:09
Here's the result: id | select_type| table| type| possible_keys| key| key_len| ref| rows| Extra 1 |SIMPLE| wp_transactions_log| index| objectid| objectid| 402| NULL| 5000| Using where; Using index – M.Abulsoud Nov 20 '16 at 20:20

score 0 · Accepted Answer · answered Nov 21 '16 at 08:51

0

The query are fast it take to 200ms to exectue, but the time for processing the query and retrieving the data are the long. I think there's no way to reduce this time.

answered Nov 21 '16 at 08:51

M.Abulsoud

989
7
23

Can some one tell me Why This is Query are very slow?

2 Answers2