mysql - Which index to add here? -

this query ends in slow query log because join without index. index add , where? simple query takes anywhere 500ms 1,2s. guess should complete within 100ms.

select users.*, user_roles.apirequests, user_roles.downloadrequests, now()             users             inner join user_roles on user_roles.id = users.role             users.rsstoken =             '775e155c780ed5af9119f797f814c714' limit 1; 

see query , show create tables: https://kopy.io/icz1z

for query:

select u.*, ur.apirequests, ur.downloadrequests, now() users u inner join      user_roles ur      on ur.id = u.role u.rsstoken = '775e155c780ed5af9119f797f814c714'; 

the best indexes users(rsstoken, role) , user_roles(id). have second index, because id declared primary key.

you can include apirequests , downloadrequests in index on user_roles: user_roles(id, apirequests, downloadrequests). might small optimization -- counsel against because id primary key , row size small.