MySQL Forums
Forum List  »  Optimizer & Parser

Optimization advise needed
Posted by: Jerry Tian
Date: May 18, 2006 12:51AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Optimization advise needed
2526
May 18, 2006 12:51AM
1861
May 18, 2006 12:57PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.