-1

I have a program that reads a set of entries from a database, performs some set of operations on these entries, and updates them in the database, the select query is pretty straight forward, It just selects the oldest updated top 200 entries. The query also ignores any entries that were marked as "Deleted".

Now the weird thing is that query was running perfectly fine for a couple of days, with execution time, not exceding 200ms, but all of a sudden the execution time jumped to over 15 seconds and stayed like that.

SELECT 
    `x`.`Id`, `x`.`InternalId`
FROM
    `my_entry` AS `x`
WHERE (`x`.`IsDeleted` <> TRUE OR `x`.`IsDeleted` IS NULL)
ORDER BY `x`.`UpdatedDate`
LIMIT 200;

Table structure:

Id int(11) AI PK 
InternalId varchar(30) 
UpdatedDate datetime
IsDeleted bit(1)

I also have a BTREE index on the UpdatedDate column

Any leads on this problem, or where should I be looking for more details?

Thanks

  • Share the explain execution plan and edit your post. like explain select ... – James Oct 16 '19 at 4:26
  • 2
    1) CREATE INDEX idx ON my_entry (IsDeleted, UpdatedDate) 2) Replace <> TRUE with = False 3) Recommendation. Update IsDeleted, convert NULLs to False, then set its DEFAULT to False and define it as NOT NULL. – Akina Oct 16 '19 at 5:13
0

The way the query is written, and the way IsDeleted is implemented, make the use of any index on IsDeleted useless. (Akina's comment explains h

If you have INDEX(UpdatedDate) then that index may help in the query. (Please provide EXPLAIN SELECT ....) But, if there are a lot of rows that fail that IsDeleted test, then it would have to scan a lot of rows to find 200. This might be the answer to the sluggishness.

Please provide SHOW CREATE TABLE.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.