Rows_examined: 439377 ?

KashKurcura

Hello:

In my slow query log i have an entry like this below, what I dont really
understand this Rows_examined: 439377

It is only calling two tables 'Selections (146857 rows)' and 'Games (73,260
rows)'. Toghether they dont even add up to 439377.

Query_time: 36 Lock_time: 0 Rows_sent: 0 Rows_examined: 439377

SELECT PlayerID, SelectionID, ContestID
FROM Selections
LEFT JOIN Games ON ( Selections.GameID = Games.GameID )
WHERE GameDate = '2004-11-23'
ORDER BY PlayerID

Can someone please help me out?

tsinka

Hi,

in most cases that means that MySQL does full table scans in some way because of missing indexes.

Mae sure that you have an index on the following fields:

Selections.GameID
Games.GameID
GameDate
PlayerID

You can check how MySQL uses indexes (and which indexes) with

EXPLAIN <sql_query>

Thomas

KashKurcura

I dont really understand the how EXPLAIN works, but this is what was retuned

Selections ALL NULL NULL NULL NULL 147280 Using filesort
Games eq_ref PRIMARY,B,D PRIMARY 3 Selections.GameID 1 Using where

I am missing an index on Selections.GameID, i thought indexs only had to be done for fields in the where cluase?

tsinka

It's not that easy in every case to find the fields that need an index. Especially on LEFT JOINS you should also check all the ON parts (they're some kind of WHERE clause). If MySQL uses indexes at all also depends on the data stored in a table. MySQL doesn't use indexes if e.g. the query analysis shows that even with using an index MySQL would read a high percentage of the rows in a table. In that case it might be faster to do a full table scan instead of using the index.

There's a chapter in the MySQL manual about how MySQL uses indexes. There's also a section about EXPLAIN. It's very important to make sure that you set up the indexes correctly.

Having e.g. too much indexes on a table can decrease performance, too.

EDIT: So my last post wasn't completely correct ... don't make sure that you have an index on that fields ... make sure to check if you need an index on that fields ...

Thomas