-1

My db query is taking 5 seconds instead of <1 second earlier and the culprit query is :

explain extended 
select A,B,C 
from table flow 
where 
(status in ('X' , 'Y' , 'Z')) 
 and priority=1 
 and created<=now() 
 order by id asc limit 10;

Output:

Output

Indexed:

create index status_created on flow (status, created);

Primary Index on primary key Id.

What does filtered column telling me here with 61839 value?

NickW
  • 8,430
  • 2
  • 6
  • 19
user124
  • 423
  • 2
  • 7
  • 26
  • `where where` - please take the time to proof-read what you have posted. – Stu Apr 26 '22 at 20:00
  • i was masking private details,so might have skipped that.Updated – user124 Apr 26 '22 at 20:02
  • Are you sure that output is correct? The [filtered column](https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain_filtered) is a percentage. Its maximum value is 100. – Bill Karwin Apr 26 '22 at 20:07
  • yeah.But this is what I am getting. Saw some similar post https://stackoverflow.com/questions/22969672/mysql-explain-extended-filtered-column-obviously-its-not-a-percentage , but it did not help – user124 Apr 26 '22 at 20:08
  • Is it even necessary to have a condition on `created <= now()`? I would think any row that exists has already been created. I.e. what does it mean for a row to have a `created` value in the future? – Bill Karwin Apr 26 '22 at 20:09
  • I would add an index on columns `(priority, status, created)` in that order. I'm assuming that the condition on `created` is likely to match all rows anyway, so the condition on `status` may be more effective at restricting rows. Possibly the condition on `created` will be handled by index condition pushdown. – Bill Karwin Apr 26 '22 at 20:11
  • yeah.I was thinking in same direction.But I am doubtful that why it is using PRIMARY index as of now instead of status_created index which helps to reduce data set size to scan. And why does it even need PRIMARY index for this query – user124 Apr 26 '22 at 20:14
  • When it says `type: index` it is an index-scan, not a lookup. So it's actually examining every entry in the primary index, i.e. the clustered index. This is nearly as costly as a table-scan. It might be avoiding the index on `status_created` if the values you are searching for match a large portion of the rows anyway. The optimizer can do that if it estimates that it's needless overhead to use the index, since doing a table-scan or index-scan is more direct. – Bill Karwin Apr 26 '22 at 20:28
  • I found this: "*EXPLAIN EXTENDED shows bogus value for 'filtered' column for LIMIT query*" https://bugs.mysql.com/bug.php?id=34124 Are you using a version of MySQL older than 5.7.7? – Bill Karwin Apr 26 '22 at 20:30
  • mysql is 5.1.x version. thanks for that link.I can ignore that filtered column. I tried to use FORCE status_created and got rows as 62673. It means it returns more rows thats why sql ignores it. But how come type: index, i.e i.e. the clustered index is referring just 89 rows as you see output in above questions.If its a full table scan then it should show rows as 120,000 as these are approx values in my table. How can that clustering index even help here ?as there is not condition on that. – user124 Apr 26 '22 at 20:37
  • does 89 means sql will just examine 89 rows out of 120k rows and it knows about those rows with help of index?Or what does this mean – user124 Apr 26 '22 at 20:40

1 Answers1

0
INDEX(priority, status)  -- column tested with "=" comes first.

Are there really any rows with created >= NOW()? If so, perhaps "created" is a poor name for the column.

Rick James
  • 135,179
  • 13
  • 127
  • 222