Re: why must i specify an index in query?
The purpose of the query is to pull all the users and join their last page accessed.
The query you posted does not return a result set, because the join query (select * from userHits order by id desc limit 1) will only fetch the last insert in userHits, not the first result when joined with ON.
If the LIMIT is removed, then it will return all the results, but it includes every userHit when I only want to join the last user hit to each user.
The query I am using works, but I don't understand why USE INDEX is necessary.
Because a JOIN cannot use the users.id column, I had to use a subquery to find the id the of the userHit instead.
SELECT * FROM users ### fetch all users
LEFT JOIN (SELECT * FROM userHits) tx1 ON tx1.id= ### join last hit on ID of hit (not userId)
(SELECT id FROM userHits USE INDEX(userId) WHERE userId=users.id ORDER BY id DESC LIMIT 1) ### last hit ID
Subject
Views
Written By
Posted
1749
July 02, 2015 10:31AM
885
July 02, 2015 11:46AM
1537
July 02, 2015 12:46PM
991
July 02, 2015 01:58PM
Re: why must i specify an index in query?
1017
July 02, 2015 02:49PM
937
July 02, 2015 09:47PM
905
July 02, 2015 10:36PM
1044
July 03, 2015 08:10AM
935
July 03, 2015 03:06PM
902
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.