why must i specify an index in query?
SELECT * FROM users LEFT JOIN (SELECT * FROM userHits) tx1 ON tx1.id=(SELECT id FROM userHits USE INDEX(userId) WHERE userId=users.id ORDER BY id DESC LIMIT 1);
0.91 seconds
SELECT * FROM users LEFT JOIN (SELECT * FROM userHits) tx1 ON tx1.id=(SELECT id FROM userHits WHERE userId=users.id ORDER BY id DESC LIMIT 1);
~15 seconds
**I realize the cardinality is low (its at 2), which is why MySQL isn't using it, but if a cardinality of 2 speeds up a query by 15x it seems sensible that it should use all indexes even if the cardinality is low. **
Can I configure this in my.cnf to force all indexes be used? Is there a way around this besides specifying the index in the query?
These are all innodb tables, so I thought it best to be in this forum. Excuse me if I was wrong.
Thanks!
Subject
Views
Written By
Posted
why must i specify an index in query?
1920
July 02, 2015 10:31AM
972
July 02, 2015 11:46AM
1642
July 02, 2015 12:46PM
1152
July 02, 2015 01:58PM
1095
July 02, 2015 02:49PM
1052
July 02, 2015 09:47PM
995
July 02, 2015 10:36PM
1153
July 03, 2015 08:10AM
1042
July 03, 2015 03:06PM
1004
July 03, 2015 09:12PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.