Menu
The use of the "FORCE/USE/IGNORE INDEX" when doing a straightforward select is well-documented, but it's not clear from the documentation how to do it for a JOIN.
How do you force a specific index to be used for a joined table?
376
Pre-requisites: You have to create a database table with data in a MySQL database to check the Force Index feature of MySQL. Open the terminal and connect with the MySQL server by executing the following command. Run the following command to create a database named test_db.
How the Force Index Works. The force index is calculated by subtracting yesterday's close from today's close and multiplying the result by today's volume. If closing prices are higher today than yesterday, the force is positive. If closing prices are lower than yesterday's, the force is negative.
SELECT table1. column1, table2. column2... FROM table1 FULL JOIN table2 ON table1.
MySQL only supports using a single index per join. If you want it to utilize two columns as indices in the join, you should create a single index over those two columns. Note that this isn't as bad as it seems, because an index over (a,b) doubles as an index over just a.
The FORCE/USE/IGNORE goes after the table name you are joining, and after the alias if you're using one.
SELECT t1.`id` AS `id_1`, t2.`id` AS `id_2` FROM `table1` t1 LEFT OUTER JOIN `table2` t2 FORCE INDEX FOR JOIN (`table1_id`) ON (t2.`table1_id` = t1.`id`) 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