Difference between the USE INDEX, IGNORE INDEX, FORCE INDEXSummaryThe purpose of this KB is to understand the difference between the USE INDEX, IGNORE INDEX, FORCE INDEX when using Index Hints in MYSQL. Index hints can enhance query performance in the MySQL database.InstructionsMySQL's optimizer chooses the fastest index option for queries automatically, but some times we experience slowness while trying to fetch the data from the database and that could be due to a bad query wherein we can fine-tune sql-query by hinting a proper index.The effectiveness of a database server is greatly impacted by the selection of the index. Indexes enable you to specify your desired search order and help your queries concentrate on the relevant rows. Covering indexes, also known as index-only queries, expedite processes by responding to database requests without requiring access to the actual data in the tables.To specify which indexes the optimizer uses or doesn't use.One of the three index hints—USE INDEX, IGNORE INDEX, or FORCE INDEX—can be utilized.Related LinksThe three syntax options for hints are USE INDEX, IGNORE INDEX, and FORCE INDEX: USE_INDEX The first (USE INDEX) instructs MySQL to use only the index listed choose indexes during query processingThe USE INDEX hint tells MySQL to use only one of the named indexes to find rows in the table USE INDEX with example below. ----------- SELECT task0.`sys_id` FROM (task task0 use index(CHG4239732_index2) LEFT JOIN task task1 ON task0.`parent` = task1.`sys_id` ) WHERE task0.`sys_class_name` = 'sysapproval_group' AND task0.`state` IN (-5 , 1 , 2) AND task0.`active` != 0 AND task0.`assignment_group` IN ('1da5000513591f44a6b576d66144b091' , 'f333414813080784a6b576d66144b007' , '52734350134ad7ccf05c7e276144b049' , 'fa738350134ad7ccf05c7e276144b08d' , 'd6734350134ad7ccf05c7e276144b046' , '9f34090c13080784a6b576d66144b00e' , 'a6a583476fea1a408cb990264b3ee4ba' , '32738350134ad7ccf05c7e276144b08b') AND task1.`number` LIKE 'CHG%' ORDER BY task0.`sys_updated_on` DESC limit 0,30 <br/><br/>Result set includes 0 rows <br/>Query time 98 milliseconds ------>> SQL it has enormously cut down to 98 milliseconds from 3 seconds ------------ IGNORE_INDEX The second (IGNORE INDEX) prevents MySQL from using the indexes listed;The alternative syntax IGNORE INDEX tells MySQL to not use some particular index or indexes. IGNORE INDEX with example below. ------------- SELECT task0.`sys_id` FROM (task task0 ignore index(wnduigoi_index4) LEFT JOIN task task1 ON task0.`parent` = task1.`sys_id` ) WHERE task0.`sys_class_name` = 'sysapproval_group' AND task0.`state` IN (-5 , 1 , 2) AND task0.`active` != 0 AND task0.`assignment_group` IN ('1da5000513591f44a6b576d66144b091' , 'f333414813080784a6b576d66144b007' , '52734350134ad7ccf05c7e276144b049' , 'fa738350134ad7ccf05c7e276144b08d' , 'd6734350134ad7ccf05c7e276144b046' , '9f34090c13080784a6b576d66144b00e' , 'a6a583476fea1a408cb990264b3ee4ba' , '32738350134ad7ccf05c7e276144b08b') AND task1.`number` LIKE 'CHG%' ORDER BY task0.`sys_updated_on` DESC limit 0,30 /* thomsonreuters005, gs:5715F8B013713BC8A6B576D66144B06C, tx:a0f42d7013fd7bc8a6b576d66144b012 */<br/>Result set includes 0 rows<br/>Query time 67 milliseconds----->>ignore has improved the execution of the query ------------- FORCE_INDEX The third (FORCE INDEX) has the same effect as the first option, but with the added limitation that table scans occur only when none of the given indexes can be used and it always forces to use given index.The FORCE INDEX hint acts similar to USE INDEX with the addition that a table scan is assumed to be very expensive which means a table scan is used only if there is no way to use one of the named indexes to find rows in the table. FORCE INDEX with example below. ------------ SELECT cmdb0.u_guid, cmdb0.u_service_provider, cmdb0.operational_status, cmdb0.u_dataset_nm, cmdb0.u_automated_validation_state, cmdb0.sys_updated_on, cmdb0.u_proposed_state, cmdb0.discovery_source, cmdb0.first_discovered, cmdb0.due_in, cmdb0.u_task, cmdb0.gl_account, cmdb0.invoice_number, cmdb0.sys_created_by, cmdb0.warranty_expiration, cmdb0.u_proposed_type, cmdb0.x_pd_i9n_pagerduty_webhook AS v_c3aba5820, cmdb0.owned_by, cmdb0.checked_out, cmdb0.sys_domain_path, cmdb0.u_failure_justification, cmdb0.u_description, cmdb0.maintenance_schedule, cmdb0.cost_center, cmdb0.dns_domain, cmdb0.assigned, cmdb0.x_pd_i9n_pagerduty_service AS v_f0fa75c01, cmdb0.purchase_date, cmdb0.short_description, cmdb0.u_barcode, cmdb0.managed_by, cmdb0.last_discovered, cmdb0.can_print, cmdb0.sys_class_name, cmdb0.manufacturer, cmdb0.u_reconciliation_status, cmdb0.u_last_hw_scan_time, cmdb0.u_manual_validation_state, cmdb0.vendor, cmdb0.u_capability_type, cmdb0.model_number, cmdb0.u_secondary_dns, cmdb0.u_last_security_scan, cmdb0.assigned_to, cmdb0.start_date, cmdb0.u_risk_level, cmdb0.serial_number, cmdb0.u_axp_validation_faliure, cmdb0.u_infra_risk_level, cmdb0.support_group, cmdb0.u_blazent_key, cmdb0.u_dqa_comment, cmdb0.unverified, cmdb$par10.correlation_id, cmdb0.attributes, cmdb0.u_axp_normalization_failure, cmdb0.asset, cmdb0.u_recon_characteristics, cmdb0.skip_sync, cmdb0.u_certification_failed_reason, cmdb0.sys_updated_by, cmdb0.u_last_boot_time, cmdb0.u_encryption_indicator, cmdb0.sys_created_on, cmdb0.sys_domain, cmdb0.u_active, cmdb0.u_proposed_date, cmdb0.install_date, cmdb$par10.u_business_risk_level, cmdb0.asset_tag, cmdb0.u_blz_os, cmdb$par10.fqdn, cmdb0.change_control, cmdb0.u_validation_errors, cmdb0.u_parent, cmdb0.u_dc_last_cert_date, cmdb0.delivery_date, cmdb0.install_status, cmdb0.supported_by, cmdb0.name, cmdb0.subcategory, cmdb0.u_infrastructure_indicator, cmdb0.u_primary_dns, cmdb0.u_last_sw_scan_time, cmdb0.assignment_group, cmdb0.u_name_class, cmdb0.u_release_environment, cmdb0.sys_id, cmdb0.po_number, cmdb0.sys_class_path, cmdb0.checked_in, cmdb0.mac_address, cmdb0.company, cmdb0.justification, cmdb0.department, cmdb0.cost, cmdb0.comments, cmdb0.u_last_domain_authentication, cmdb0.sys_mod_count, cmdb0.u_gateway, cmdb0.monitor, cmdb0.model_id, cmdb0.ip_address, cmdb0.cost_cc, cmdb0.schedule, cmdb0.order_date, cmdb0.u_data_security_classification, cmdb$par10.u_native_asset_id, cmdb0.due, cmdb0.location, cmdb0.category, cmdb0.fault_count, cmdb0.lease_id FROM (cmdb cmdb0 force index(name) INNER JOIN cmdb$par1 cmdb$par10 ON cmdb0.sys_id = cmdb$par10.sys_id ) WHERE cmdb0.sys_class_path LIKE '/!!%' AND cmdb0.name IS NOT NULL AND cmdb0.u_dataset_nm = 'Actual' AND (cmdb0.operational_status IN (5 , 13 , 14 , 15 , 12 , 16 , 17 , 26 , 27 , 7 , 9 , 28 , 29 , 21 , 22 , 23 , 24 , 25) OR cmdb0.operational_status IS NULL ) AND cmdb0.u_active = 1 AND cmdb0.sys_class_name != 'u_incident_category' AND cmdb0.sys_class_name != 'cmdb_ci_dns_name' AND cmdb0.sys_class_name != 'cmdb_ci_snc_component' AND cmdb0.u_active = 1 AND (cmdb0.u_dataset_nm = 'Actual' OR cmdb0.u_dataset_nm = 'Proposed') ORDER BY cmdb0.name limit 0,1<br/><br/>Result set includes 1 rows<br/>Query time 98 milliseconds ---->>; Forcing index improved enormously from 1 min 13 sec to 98 milli sec -----------