Hi,
I am having trouble trying to figure out the reason of this. The query (explained) is:
EXPLAIN SELECT * FROM news,users WHERE news.user_id=users.user_id ORDER BY date DESC
I divided the result table in two parts to improve readability:
+----+-------------+--------------+------+---------------+
| id | select_type | table | type | possible_keys |
+----+-------------+--------------+------+---------------+
| 1 | SIMPLE | news | ALL | [NULL] |
| 1 | SIMPLE | users | ALL | PRIMARY |
+----+-------------+--------------+------+---------------+
+--------+---------+--------+------+---------------------------------+
| key | key_len | ref | rows | Extra |
+--------+---------+--------+------+---------------------------------+
| [NULL] | [NULL] | [NULL] | 5000 | Using temporary; Using filesort |
| [NULL] | [NULL] | [NULL] | 3 | Using where |
+--------+---------+--------+------+---------------------------------+
I have an index on the date column (that is on the news table), but it looks like
MySQL is not
using it to get the rows sorted.
I tried the query using LEFT JOIN, and this way MySQL uses the index:
EXPLAIN SELECT * FROM news LEFT JOIN users USING (user_id) ORDER BY date DESC
+----+-------------+--------------+--------+---------------+
| id | select_type | table | type | possible_keys |
+----+-------------+--------------+--------+---------------+
| 1 | SIMPLE | news | index | [NULL] |
| 1 | SIMPLE | users | eq_ref | PRIMARY |
+----+-------------+--------------+--------+---------------+
+---------+---------+-------------------+------+-------+
| key | key_len | ref | rows | Extra |
+---------+---------+-------------------+------+-------+
| date | 4 | [NULL] | 5000 | |
| PRIMARY | 2 | mydb.news.user_id | 1 | |
+---------+---------+-------------------+------+-------+
Also the query time drops by 1/4 in a 5000 rows table. I am not sure if I want to use
LEFT JOIN
only to optimize the results (in my case it doesn't matter if it is an INNER or LEFT
JOIN, but a
INNER JOIN looks more *natural*).
I would like to see an explanation of this behaviour, and advise about this, because I
have been
trying to find the clue for lot of time without luck.
Thanks,
IrYoKu
______________________________________________
Renovamos el Correo Yahoo!: ˇ100 MB GRATIS!
Nuevos servicios, más seguridad
http://correo.yahoo.es
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
