Optimization advise needed

[Jerry Tian]

I am in the middle of developing a databse using myisam engine. here is my server version information.

--------------
mysql Ver 14.12 Distrib 5.0.20, for pc-linux-gnu (i486) using readline 5.1

Connection id: 1346
Current database:
Current user: dev@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.0.20-Debian_1-log
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/run/mysqld/mysqld.sock
Uptime: 13 hours 27 min 3 sec

Threads: 1 Questions: 426714 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 50 Queries per second avg: 8.812
--------------

and this is the schema of my database.
-- phpMyAdmin SQL Dump
-- version 2.8.0.3-Debian-1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: May 18, 2006 at 02:36 PM
-- Server version: 5.0.20
-- PHP Version: 5.1.2-1+b1
--
-- Database: `msnx-test`
--

-- --------------------------------------------------------

--
-- Table structure for table `MSN_GROUP`
--

CREATE TABLE `MSN_GROUP` (
`OWNER` varchar(50) NOT NULL default '',
`GROUP_ID` varchar(30) NOT NULL,
`INTERNAL_ID` int(3) unsigned NOT NULL default '0',
`GROUP_NAME` varchar(50) NOT NULL,
KEY `OWNER` (`OWNER`),
KEY `GROUP_ID` (`GROUP_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `MSN_SERVER`
--

CREATE TABLE `MSN_SERVER` (
`ID` int(5) unsigned NOT NULL default '0',
`ADDRESS` varchar(20) NOT NULL,
`PORT` int(5) unsigned NOT NULL default '0',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `MSN_USER`
--

CREATE TABLE `MSN_USER` (
`ACCOUNT` varchar(50) NOT NULL,
`WVID` int(10) unsigned NOT NULL default '0',
`PASSWORD` varchar(20) NOT NULL default '',
PRIMARY KEY (`ACCOUNT`),
KEY `WVID` (`WVID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `MSN_USER_FRIENDS`
--

CREATE TABLE `MSN_USER_FRIENDS` (
`HOST` varchar(50) NOT NULL default '',
`FRIEND` varchar(50) NOT NULL,
`GROUP_ID` varchar(30) NOT NULL,
`FRIEND_NAME` varchar(50) NOT NULL,
`INTERNAL_ID` int(5) unsigned NOT NULL default '0',
KEY `HOST` (`HOST`),
KEY `FRIEND` (`FRIEND`),
KEY `GROUP_ID` (`GROUP_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

now i want a query to retrieve all the records in MSN_USER_FRIENDS table of which HOST are in the MSN_USER table with the specified WVID and GROUP_ID in the MSN_GROUP table with the specified INTERNAL_ID. i have test a few queries, here they are:

EXPLAIN SELECT *
FROM MSN_USER_FRIENDS AS F, MSN_USER AS U, MSN_GROUP AS G
WHERE F.HOST = U.ACCOUNT
AND G.OWNER = U.ACCOUNT
AND F.GROUP_ID = G.GROUP_ID
AND U.WVID =11164
AND G.INTERNAL_id = 1
+----+-------------+-------+------+----------------+-------+---------+---------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+----------------+-------+---------+---------------------+------+-------------+
| 1 | SIMPLE | U | ref | PRIMARY,WVID | WVID | 4 | const | 1 | |
| 1 | SIMPLE | G | ref | OWNER,GROUP_ID | OWNER | 152 | msnx-test.U.ACCOUNT | 2 | Using where |
| 1 | SIMPLE | F | ALL | HOST,GROUP_ID | | | | 3 | Using where |
+----+-------------+-------+------+----------------+-------+---------+---------------------+------+-------------+
EXPLAIN SELECT *
FROM MSN_USER_FRIENDS AS F
INNER JOIN MSN_USER AS U ON F.HOST = U.ACCOUNT
AND U.WVID =11164
INNER JOIN MSN_GROUP AS G ON G.OWNER = U.ACCOUNT
AND G.INTERNAL_id =1
WHERE F.HOST = U.ACCOUNT
AND F.GROUP_ID = G.GROUP_ID
+----+-------------+-------+------+----------------+-------+---------+---------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+----------------+-------+---------+---------------------+------+-------------+
| 1 | SIMPLE | U | ref | PRIMARY,WVID | WVID | 4 | const | 1 | |
| 1 | SIMPLE | G | ref | OWNER,GROUP_ID | OWNER | 152 | msnx-test.U.ACCOUNT | 2 | Using where |
| 1 | SIMPLE | F | ALL | HOST,GROUP_ID | | | | 3 | Using where |
+----+-------------+-------+------+----------------+-------+---------+---------------------+------+-------------+

EXPLAIN SELECT *
FROM MSN_USER_FRIENDS AS F
WHERE F.HOST
IN (
SELECT U.ACCOUNT
FROM MSN_USER AS U
WHERE U.WVID =11164
)
AND F.GROUP_ID
IN (
SELECT G.GROUP_ID
FROM MSN_GROUP AS G, MSN_USER U
WHERE G.OWNER = U.ACCOUNT
AND G.INTERNAL_ID =1
AND U.WVID =11164
)
+----+--------------------+-------+----------------+----------------+---------+---------+---------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+----------------+----------------+---------+---------+---------------------+------+--------------------------+
| 1 | PRIMARY | F | ALL | | | | | 3 | Using where |
| 3 | DEPENDENT SUBQUERY | U | ref | PRIMARY,WVID | WVID | 4 | const | 1 | |
| 3 | DEPENDENT SUBQUERY | G | ref | OWNER,GROUP_ID | OWNER | 152 | msnx-test.U.ACCOUNT | 2 | Using where |
| 2 | DEPENDENT SUBQUERY | U | index_subquery | PRIMARY,WVID | PRIMARY | 152 | func | 1 | Using index; Using where |
+----+--------------------+-------+----------------+----------------+---------+---------+---------------------+------+--------------------------+
EXPLAIN SELECT *
FROM MSN_USER_FRIENDS AS F, MSN_USER AS U
WHERE F.HOST = U.ACCOUNT
AND U.WVID =11164
AND F.GROUP_ID
IN (
SELECT G.GROUP_ID
FROM MSN_GROUP AS G, MSN_USER AS U
WHERE G.OWNER = U.ACCOUNT
AND U.WVID =11164
AND G.INTERNAL_ID =1
)
+----+--------------------+-------+------+----------------+-------+---------+---------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+------+----------------+-------+---------+---------------------+------+-------------+
| 1 | PRIMARY | U | ref | PRIMARY,WVID | WVID | 4 | const | 1 | |
| 1 | PRIMARY | F | ref | HOST | HOST | 152 | msnx-test.U.ACCOUNT | 2 | Using where |
| 2 | DEPENDENT SUBQUERY | U | ref | PRIMARY,WVID | WVID | 4 | const | 1 | |
| 2 | DEPENDENT SUBQUERY | G | ref | OWNER,GROUP_ID | OWNER | 152 | msnx-test.U.ACCOUNT | 2 | Using where |
+----+--------------------+-------+------+----------------+-------+---------+---------------------+------+-------------+

now, only the last query doesn't need a full table scan on MSN_USER_FRIENDS table, but using a sub query. the problem of the first two quries are the engine seems doesn't want to use the HOST index as a reference in the MSN_USER_FRIENDS table when executing the join query. is there a solution to this problem?

thanks in advance.



Edited 1 time(s). Last edit at 05/18/2006 01:07AM by Jerry Tian.