Query examining way more than expected..

[Curt]

Hey everyone,

I recently enabled slow query logging so i can pinpoint troublesome queries..

I seem to have some select queries from the HOF script that are examining like 17mill rows for some reason...

I have reviewed the query but cant figure out why it would be doing this...I even changed the query around a bit...

here is the query:

SELECT u.userid,us.strength FROM users u LEFT JOIN userstats us ON u.userid=us.userid WHERE u.userid!=1 AND u.laston>0 ORDER BY us.strength DESC,u.userid ASC LIMIT 50;

 

I dont understand why its examining a huge amount of non-existence rows...

anyone else had a similar problem ?

[a_bertrand]

It examines tons of rows when: you don't have indexes or mysql can't use them. Try the "explain" function with the query and you shall see how MySQL work under to execute the query. BTW the "non-existent" rows are basically the cross product of 2 tables:

Table A you have:

a, b, c

Table B you have:

1, 2, 3

The cross product is:

a1, a2, a3, b1, b2, b3, c1, c2, c3

As you see there is a lot more rows in the cross product that in the 2 other tables ;)

[Curt]

ah i understand now...yea the userstats table does not have an index..

thanks for the greatly helpful advice :)