Using 3.23.44 compiled from source from mysql.com on Linux Kernel 2.4.16
from RedHat distro.
I'm using a cross table to support many-to-many relationship between two
tables.
EXPLAIN shows that two of the joins aren't optimized.
All columns used in the two straight joins are of the same type and are all
indexed.
Any constructive suggestions would be appreciated.
A mysqldump and EXPLAIN SELECT are included after signoff.
If anyone would like to suggest a more efficient way to maintain a
many-to-many relationship, let me know.
Thanks-in-advance,
--jk
#
# Table structure for table 'role'
#
CREATE TABLE role (
id int(11) NOT NULL auto_increment,
name varchar(50) NOT NULL default '',
description text,
PRIMARY KEY (id),
KEY id (id),
KEY name (name)
) TYPE=MyISAM;
#
# Dumping data for table 'role'
#
INSERT INTO role VALUES (1,'siteAdmin','');
INSERT INTO role VALUES (2,'engineeringAdmin','');
INSERT INTO role VALUES (3,'dir4Admin','');
INSERT INTO role VALUES (4,'user','');
#
# Table structure for table 'user'
#
CREATE TABLE user (
id int(11) NOT NULL auto_increment,
username varchar(50) NOT NULL default '',
password varchar(50) NOT NULL default '',
email varchar(100) NOT NULL default '',
PRIMARY KEY (id),
KEY id (id),
KEY username (username)
) TYPE=MyISAM;
#
# Dumping data for table 'user'
#
INSERT INTO user VALUES (1,'jkraai','password','[EMAIL PROTECTED]');
INSERT INTO user VALUES
(2,'siteRoot','password','[EMAIL PROTECTED]');
INSERT INTO user VALUES
(3,'engAdmin','password','[EMAIL PROTECTED]');
INSERT INTO user VALUES
(4,'dir4Admin','password','[EMAIL PROTECTED]');
INSERT INTO user VALUES
(5,'genericUser','password','[EMAIL PROTECTED]');
#
# Table structure for table 'userXrole'
#
CREATE TABLE userXrole (
userId int(11) NOT NULL default '0',
roleId int(11) NOT NULL default '0',
KEY userId (userId),
KEY roleId (roleId)
) TYPE=MyISAM;
#
# Dumping data for table 'userXrole'
#
INSERT INTO userXrole VALUES (1,4);
INSERT INTO userXrole VALUES (1,1);
INSERT INTO userXrole VALUES (2,1);
INSERT INTO userXrole VALUES (3,2);
INSERT INTO userXrole VALUES (4,3);
INSERT INTO userXrole VALUES (5,4);
INSERT INTO userXrole VALUES (1,3);
# get roles per user
EXPLAIN
SELECT
user.username,role.name
FROM
user,userXrole,role
WHERE
userXrole.userId=user.id and
role.id=userXrole.roleId;
#yields:
#+-----------+--------+---------------+---------+---------+-----------------
-+------+------------+
#| table | type | possible_keys | key | key_len | ref
| rows | Extra |
#+-----------+--------+---------------+---------+---------+-----------------
-+------+------------+
#| userXrole | ALL | userId,roleId | NULL | NULL | NULL
| 7 | |
#| role | ALL | PRIMARY,id | NULL | NULL | NULL
| 4 | where used |
#| user | eq_ref | PRIMARY,id | PRIMARY | 4 | userXrole.userId
| 1 | |
#+-----------+--------+---------------+---------+---------+-----------------
-+------+------------+
#3 rows in set (0.00 sec)
# when adding a where clause to limit the results to one user, it gets no
better:
EXPLAIN
SELECT
user.username,role.name
FROM
user,userXrole,role
WHERE
userXrole.userId=user.id and
role.id=userXrole.roleId
and user.username='jkraai';
#yields
#+-----------+------+---------------------+----------+---------+-------+----
--+------------+
#| table | type | possible_keys | key | key_len | ref |
rows | Extra |
#+-----------+------+---------------------+----------+---------+-------+----
--+------------+
#| user | ref | PRIMARY,id,username | username | 50 | const |
1 | where used |
#| userXrole | ALL | userId,roleId | NULL | NULL | NULL |
7 | where used |
#| role | ALL | PRIMARY,id | NULL | NULL | NULL |
4 | where used |
#+-----------+------+---------------------+----------+---------+-------+----
--+------------+
#3 rows in set (0.00 sec)
---------------------------------------------------------------------
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
---------------------------------------------------------------------
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
