MySql Indexing Problem

[jeffsbaker]

I was wondering if someone could help me with a mysql indexing problem.
The index of 'username' for this code works fine:

EXPLAIN SELECT count( * ) AS num_messages
FROM messages, users
WHERE messages.username = 'johndoe'
AND users.username = 'johndoe'
AND messages.sent_date >= users.last_activity

Here is the explain:

id  select_type  table    type  possible_keys  key      key_len  ref   rows  Extra  
1   SIMPLE       users    ref   username       username 53       const 1     Using where 
1   SIMPLE       messages ref   username       username 53       const 195   Using where

But in the following table the index of 'page' which corresponds to the users.id is not working:

EXPLAIN SELECT count( * ) AS num_comments
FROM comments, users
WHERE users.username = 'johndoe'
AND comments.page = users.id
AND comments.sent_date
>= users.last_activity

Here is the explain:

id  select_type  table    type  possible_keys    key      key_len  ref   rows  Extra  
1   SIMPLE       users    ref   PRIMARY,username username 53       const 1     Using where 
1   SIMPLE       comments ALL   page             NULL     NULL     NULL  52    Range checked for each record (index map: 0x2)

Does anyone know why MySql is not using the index key 'page' and how I can get it to use the index?

[tesuji]

You didn't tell your database to use eventually existing indexes.

Your first explain has kind of join constraint "messages.username = 'johndoe' AND users.username = 'johndoe'". Such similar thing is missing in second explain.

I would also suggest always to join tables in such a way:
...
FROM messages JOIN users ON messages.username = user.username
...

But be aware that username (e.g. 'johndoe') usually is not unique. If so, the join constraint of your first explain and others would generate wrong results when executing the queries.

So it is mostly a good idea defining convenient primary keys and foreign keys for every table what explicitly prevents from generating wrong joins. Additional profit: your database can easily find existing indexes (they are put automatically on every primary key column by your database, thus it knows them) to speed up table scan when joining tables. If you hide such keys from the database and the database determines that large joins cannot be computed without indexes, database is forced to generate temporary indexes every time join is executed, what is sheer waste.

-- tesu

[jeffsbaker]

I'm sorry, I'm trying to follow your advice but I don't quite understand. I am using MyISAM tables so I can't use foreign keys. The queries produce correct results. They are working fine for the results. There is only one user with the username 'johndoe' and there is only one row in the 'users' table with his information. users.id is the primary key.

Now it seems that there should be an easy way to get the 'comments' table to use the index of 'page' which would be all the comments that relate to the id of 'johndoe'. I tried using JOIN like in your suggestion:

EXPLAIN SELECT count( * ) AS num_comments
FROM comments
JOIN users ON users.id = comments.page
WHERE users.username = 'johndoe'
AND comments.sent_date >= users.last_activity

But it is still not using the index 'page'. Here is the explain result:

id  select_type  table    type  possible_keys    key      key_len  ref   rows  Extra  
1   SIMPLE       users    ref   PRIMARY,username username 53       const 1     Using where 
1   SIMPLE       comments ALL   page             NULL     NULL     NULL  52    Using where; Using join buffer

[tesuji]

Explain explains that tables users and messages have both keys named username. Table comments has a key named page. Possibly this is also a foreign key related to username of table users in real world.

Indeed, myisam allows creation of foreign keys, unfortunately, it doesn't make use of them.

Maybe you can rename column page of comments into username and rebuilt index on that new column or you change over to innodb? Another idea: can you also put column page into primary key of comments? It doesn't matter whether this pk will then have more than one column.

Attention, renaming columns on improper data models (lacking of foreign keys) and on engine myisam is a very dangerous task for it could destroy your fairly intact referential integrity entirely.

-- tesu

[jeffsbaker]

Maybe you can rename column page of comments into username and rebuilt index on that new column

Yes I could reprogram my script and reprogram the database to use 'username' on comments instead of id. It just seems like a lot of work for something that seems like it could be done simply in MYSQL. Even forcing it to use the index 'page' with the following command does NOT work:

EXPLAIN SELECT count( * ) AS num_comments
FROM comments
FORCE KEY ( 
PAGE ) , users
WHERE users.username = 'johndoe'
AND comments.page = users.id
AND comments.sent_date >= users.last_activity

The easiest way to get the results I want without reprogramming all my code is to have two select statements:

SELECT id, last_activity FROM users
WHERE username = 'johndoe'

and then:

SELECT count( * ) AS num_comments
FROM comments
WHERE page = '$id'
AND sent_date >= '$last_activity'

This uses the index 'page' just fine. I just don't understand why I have to use two select statements. I suppose it is a limitation of mysql. It just is not an obvious limitation.