Kalletlamadhav/sql-optimization-env

1�

2���i��
�v�dd
lm
Z

e
dddddg
ed�
�
���dd	d	d3d���Zd
S)�)
�BaseTask�mgnrega_wildcarda�
A district officer wants to search for MGNREGA workers whose names contain the word "worker". The current query uses LIKE '%worker%' which prevents any index usage and forces a full table scan across all worker records. Rewrite the query to avoid the leading wildcard, or use an FTS-friendly approach. If the leading wildcard is unavoidable, add a compensating filter on an indexed column such as state_code to reduce the scan range.a
4        SELECT w.worker_id, w.worker_name, w.state_code, w.district_code,5               w.gram_panchayat, w.job_card_no, w.wage_rate6        FROM mgnrega_workers w7        WHERE w.worker_name LIKE '%worker%'8        ORDER BY w.state_code, w.worker_name9    �LEADING_WILDCARD�mgnrega_workerszdata/schemas/mgnrega_schema.sql�medium�z�LIKE '%word%' with a leading % cannot use a B-tree index. Try anchoring the search to a suffix: LIKE 'worker%', or add a restrictive indexed filter (e.g. state_code) before the LIKE clause.a10        -- Option 1: Change to suffix wildcard (if business logic allows)11        SELECT w.worker_id, w.worker_name, w.state_code, w.district_code,12               w.gram_panchayat, w.job_card_no, w.wage_rate13        FROM mgnrega_workers w14        WHERE w.worker_name LIKE 'Worker_%'15        ORDER BY w.state_code, w.worker_name;16 17        -- Option 2: Add indexed pre-filter to limit scan scope18        -- CREATE INDEX idx_mgnrega_worker_state ON mgnrega_workers(state_code, worker_name);19        SELECT w.worker_id, w.worker_name, w.state_code, w.district_code,20               w.gram_panchayat, w.job_card_no, w.wage_rate21        FROM mgnrega_workers w22        WHERE w.state_code IN ('MH','TG','KA')23          AND w.worker_name LIKE '%worker%'24        ORDER BY w.state_code, w.worker_name;25    )�task_id�goal�26slow_query�expected_pattern�tables�27schema_ddl�28difficulty�curriculum_level�	max_steps�hint�
reference_fixN)�tasks.base_taskr�open�read�TASK���IC:\open_env_sql_opt\sql-optimization-env\tasks\medium\mgnrega_wildcard.py�<module>r
sw�


�$�$�$�$�$�$��x��	7��(����t�5�6�6�;�;�=�=����	O
��9-�-�-���r