Menu
How do I force indexes on a query similar to this. I need to force an index on foo and bar individually.
SELECT foo.*, bar.* FROM foo INNER JOIN bar ON foo.rel_id = bar.rel_id WHERE foo.status = 1 AND bar.status = 1 
613
FORCE INDEX works by only considering the given indexes (like with USE_INDEX) but in addition it tells the optimizer to regard a table scan as something very expensive. However if none of the 'forced' indexes can be used, then a table scan will be used anyway.
Indexes can help improve the performance of a nested-loop join in several ways. The biggest benefit often comes when you have a clustered index on the joining column in one of the tables. The presence of a clustered index on a join column frequently determines which table SQL Server chooses as the inner table.
Indexes that help with a merge joinAn index on the sort keys can speed up sorting, so an index on the join keys on both relations can speed up a merge join. However, an explicit sort is often cheaper unless an index only scan can be used.
ALTER command to add and drop INDEXALTER TABLE tbl_name ADD INDEX index_name (column_list) − This adds an ordinary index in which any value may appear more than once. ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list) − This creates a special FULLTEXT index that is used for text-searching purposes.
Assuming index a exists on foo and index b on bar:
SELECT foo.*, bar.* FROM foo FORCE INDEX (a) INNER JOIN bar FORCE INDEX (b) ON foo.rel_id = bar.rel_id WHERE foo.status = 1 AND bar.status = 1 would force index selection on MySql
172
The obvious thing would be to create a covering index on rel_id and status of both tables to satisfy the join and where requirement.
What have you tried so-far?
You provide index hints but the crux of everyone answering seems to be that you shouldn't have to do that.
From MySQL 4.0.9 on, you can also use FORCE INDEX, which acts like USE INDEX (index_list) but with the addition that a table scan is assumed to be very expensive. In other words, a table scan is used only if there is no way to use one of the given indexes to find rows in the table.
29
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
