Menu
I have a MySQL query (running MySQL 5.0.88), which I'm trying to speed up. The underlying table has multiple indices and for the query in question, the wrong index is used (i_active - 16.000 rows, vs. i_iln - 7 rows).
I'm not very experienced with MySQL but read there is a use index hint, which can force mySQL to use a certain index. I'm trying it like this:
SELECT art.firma USE INDEX (i_iln) ... but this produces a MySQL error.
Question:
Can anyone tell me what I'm doing wrong? (Except running 5.0.88, which I can't change.)
328
If there is a choice between multiple indexes, MySQL normally uses the index that finds the smallest number of rows (the most selective index). If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows.
In case the query optimizer ignores the index, you can use the FORCE INDEX hint to instruct it to use the index instead. In this syntax, you put the FORCE INDEX clause after the FROM clause followed by a list of named indexes that the query optimizer must use.
Here is how you can force an index to be used with a query with the help of an index hint. In the above query, we are forcing the index FK_Sales_Invoices_AccountsPersonID to the index. We can always pass the name of the index in the WITH clause and that index will be used for the query.
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.
You missed the
FROM table Correct SQL should be:
SELECT art.firma FROM your_table USE INDEX (i_iln) WHERE .... 
select * from table use index (idx); http://dev.mysql.com/doc/refman/5.0/en/index-hints.html
40
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
