Heya, madhoriya.
This is more correct than either of the two:
-
select count(*) from defect_detail where status in ('CLOSED', 'RESOLVED', 'VERIFIED');
-
Note that the items in the IN subclause are sorted. This way, MySQL can use a binary search on the options, which is much faster than going through the list sequentially.
True, MySQL will automatically sort the items for you, but why make it do all that extra work when the values are constants?
Admittedly, when you only have three options, the time savings isn't great, but it's a good habit to get into.
The problem with this statement, which I will go ahead and 'explode' so you can get a better picture of what's going on:
-
SELECT
-
COUNT(*)
-
FROM
-
`defect_detail`
-
WHERE
-
(
-
`status` = 'RESOLVED'
-
OR
-
'CLOSED'
-
OR
-
'VERIFIED'
-
)
-
Note that MySQL will only check to see if `status` = 'RESOLVED'. When you use "OR 'CLOSED'", MySQL evaluates 'CLOSED' as true, so in essence your query is doing this:
-
SELECT
-
COUNT(*)
-
FROM
-
`defect_detail`
-
WHERE
-
(
-
`status` = 'RESOLVED'
-
OR
-
true
-
OR
-
true
-
)
-
which, of course actually counts ALL the records in the table.
Now, you could do this:
-
SELECT
-
COUNT(*)
-
FROM
-
`defect_detail`
-
WHERE
-
(
-
`status` = 'RESOLVED'
-
OR
-
`status` = 'CLOSED'
-
OR
-
`status` = 'VERIFIED'
-
)
-
but it's MUCH faster, especially as your table grows in size, to use IN instead:
-
SELECT
-
COUNT(*)
-
FROM
-
`defect_detail`
-
WHERE
-
`status`
-
IN
-
(
-
'CLOSED',
-
'RESOLVED',
-
'VERIFIED'
-
)
-
