A Monthly FIR Return That Returns 214 Instead of 187
The district crime record cell runs MySQL 8. Table fir has fir_id (primary key), ps_code, reg_date stored as VARCHAR as DD/MM/YYYY, and sections. Table accused has fir_id, name and age; a FIR may have zero accused (untraced) or several. The monthly return runs SELECT COUNT(*) FROM fir f JOIN accused a ON f.fir_id = a.fir_id WHERE f.reg_date BETWEEN '2024-01-01' AND '2024-01-31'; it prints 214, while the register shows 187 FIRs for January. A search using STR_TO_DATE on reg_date takes 40 seconds across 62,000 fir rows and 91,000 accused rows. There is no index on reg_date and none on accused.fir_id. A clerk suggests simply subtracting 27 from the query result so that it matches the register, and the same query is reused every month. Nightly backup runs at 02:00 and the report is due at 11:00.
1. Explain the 214 and write the correct query.
Read the worked answer
The join changes the unit of counting. COUNT(*) counts rows of the joined result, not FIRs.
- Upward pressure: a FIR with four accused produces four rows. Counting rows multiplies it by four.
- Downward pressure: an inner JOIN drops FIRs that have no accused row at all - exactly the untraced cases the senior officers care about.
Both errors are live at once, so 214 versus 187 is a net figure with two wrong parts; it is not "27 extra FIRs".
For the bare FIR count, drop the join:
SELECT COUNT(*) FROM fir WHERE STR_TO_DATE(reg_date,'%d/%m/%Y') BETWEEN '2024-01-01' AND '2024-01-31';
The STR_TO_DATE is mandatory because reg_date is VARCHAR in DD/MM/YYYY. The original predicate BETWEEN '2024-01-01' AND '2024-01-31' compares strings character by character against '01/01/2024', so it silently includes and excludes the wrong rows - the register may not even be 187 in that comparison.
If accused details are needed: SELECT f.fir_id, f.ps_code, COUNT(a.name) AS accused_count FROM fir f LEFT JOIN accused a ON a.fir_id = f.fir_id WHERE ... GROUP BY f.fir_id, f.ps_code; and the FIR total is COUNT(DISTINCT f.fir_id). LEFT JOIN keeps untraced FIRs; COUNT(a.name) counts only non-null accused, so an untraced FIR shows 0, not 1.
2. Why does the STR_TO_DATE search take 40 seconds, and what would you change before the 11:00 report?
Read the worked answer
Two independent costs.
- No usable index. reg_date has no index, so the query scans all 62,000 fir rows. Worse, wrapping the column in a function (
STR_TO_DATE(reg_date, ...)) makes any ordinary index on reg_date useless anyway: MySQL must evaluate the function for every row before it can compare. - Missing join index. With no index on accused.fir_id, each probe into 91,000 accused rows is a scan.
Fix at the next maintenance window, not at 10:55: add a generated stored column and index it.
ALTER TABLE fir ADD COLUMN reg_dt DATE AS (STR_TO_DATE(reg_date,'%d/%m/%Y')) STORED, ADD INDEX idx_fir_regdt (reg_dt);
Then query WHERE reg_dt BETWEEN '2024-01-01' AND '2024-01-31' - a plain column comparison that uses the index. Add INDEX idx_acc_fir (fir_id) on accused. The long-term fix is to change reg_date to DATE and constrain the entry form to one format, because today two date formats can coexist undetected.
Timing discipline: the ALTER locks or rebuilds a table; run it after the 02:00 backup completes and before the working day, test on a copy first, and never start a DDL change with the report due. If it is 10:55, run the corrected but slow query and explain the delay rather than altering a production table under deadline.
3. Evaluate the clerk's suggestion to subtract 27 from the result.
Read the worked answer
Refuse it, and put the reason in writing.
The 27 is a coincidence fitted to one month. The fan-out depends on how many accused each FIR has, and the dropout depends on how many FIRs are still untraced - both change month to month. February with many untraced FIRs and many multi-accused FIRs will need a different offset, so the hard-coded 27 will be wrong immediately and will drift without anyone noticing.
The deeper problem is that it hides the two facts the cell actually needs: how many FIRs are untraced (zero accused) and how many accused per FIR. A number that matches the register while hiding those is worse than a number that is visibly wrong, because nobody investigates it.
It is also indefensible in audit or court: a figure produced by an unexplained constant cannot be reproduced from the database.
Correct response: fix the query, run a reconciliation for January - COUNT(*) of fir for the month, count of FIRs with zero accused, average accused per FIR - and attach it to the return. If the fixed query still differs from the register, that gap is a data-entry problem (date formats, missing entries) and must be reported as one, not absorbed by arithmetic.