This is probably indexed wrong, so I thought I would put it
out to the list to see if it is.. or if I 'm just going to have to
live with it.
Anyway, I have two tables I'm joining together, and MySQL is not
using the index on the column in the second table I'm joining on.
Here is the explain and table descriptions:
mysql> explain
SELECT messages.id,messages.has_read,messages.replied,
IF(LENGTH(messages.subject)>45,CONCAT(LEFT(messages.subject,42),'...'),m
essages.subject) AS msg_subject,
IF(LENGTH(h_from.name)>35,CONCAT(LEFT(h_from.name,32),'...'),
IF(LENGTH(h_from.name)<3,'Not Supplied',h_from.name)) AS
addr_from,
h_from.addr,
DATE_FORMAT(messages.date,'%a, %m/%e/%y %l:%i%p') AS msgdate
FROM messages,h_from
WHERE (
(messages.folder_id=0) AND
(messages.owner_id=1) AND
(h_from.msg_id=messages.id)
)
ORDER BY date DESC\G
*************************** 1. row ***************************
table: messages
type: ref
possible_keys: PRIMARY,owner_key,folder_key
key: owner_key
key_len: 4
ref: const
rows: 5
Extra: where used; Using temporary; Using filesort
*************************** 2. row ***************************
table: h_from
type: ALL
possible_keys: msg_key
key: NULL
key_len: NULL
ref: NULL
rows: 19
Extra: where used
2 rows in set (0.00 sec)
mysql> desc messages;desc h_from;
+-----------+------------------+------+-----+---------------------+-----
-----------+
| Field | Type | Null | Key | Default |
Extra |
+-----------+------------------+------+-----+---------------------+-----
-----------+
| id | int(10) unsigned | | PRI | NULL |
auto_increment |
| owner_id | int(10) unsigned | | MUL | 0 |
|
| date | datetime | | MUL | 0000-00-00 00:00:00 |
|
| subject | text | YES | | NULL |
|
| body | mediumtext | YES | | NULL |
|
| has_read | tinyint(1) | YES | MUL | 0 |
|
| replied | tinyint(1) | YES | MUL | 0 |
|
| folder_id | int(10) unsigned | | MUL | 0 |
|
+-----------+------------------+------+-----+---------------------+-----
-----------+
8 rows in set (0.00 sec)
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | | PRI | NULL | auto_increment |
| msg_id | int(10) unsigned | | MUL | 0 | |
| name | varchar(128) | YES | | | |
| addr | varchar(128) | YES | | | |
+--------+------------------+------+-----+---------+----------------+
Server version: 3.23.49a-Max
(mysql,query,table,sql,thisisnotspam)
---------------------
Johnny Withers
[EMAIL PROTECTED]
p. 601.853.0211
c. 601.209.4985
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
