Dear listmembers
I am using MySQL 4.0.25.
I am trying to figure out how indexes should be defined to make the
following query as fast as possible (i.e. use indexes):
(shell script, for copy/paste)
echo "DROP TABLE IF EXISTS lrows;" | mysql test
echo "
CREATE TABLE IF NOT EXISTS lrows (
lock_id INT NOT NULL AUTO_INCREMENT,
lock_date DATETIME NOT NULL,
lock_usr VARCHAR(255) NOT NULL,
lock_db VARCHAR(255) NOT NULL,
lock_table VARCHAR(255) NOT NULL,
lock_row INT NOT NULL DEFAULT 0,
lock_pid INT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (lock_id),
INDEX lockone (lock_pid, lock_row),
INDEX locktwo (lock_row, lock_pid, lock_db(16), lock_table(16))
) TYPE=MYISAM;" | mysql test
# some sample data
echo "
INSERT INTO lrows VALUES (NULL,NOW(),'tsp','preg','mbrs',1,1000);
INSERT INTO lrows VALUES (NULL,NOW(),'tsp','preg','mbrs',2,1000);
INSERT INTO lrows VALUES (NULL,NOW(),'tsp','preg','mbrs',3,1000);
INSERT INTO lrows VALUES (NULL,NOW(),'mfu','preg','mbrs',2,9000);
INSERT INTO lrows VALUES (NULL,NOW(),'mfu','preg','mbrs',5,9000);
INSERT INTO lrows VALUES (NULL,NOW(),'sfa','dbto','othr',2,8000);
" | mysql test
# my query:
echo "EXPLAIN SELECT t2.lock_id FROM lrows AS t1
INNER JOIN lrows AS t2 ON t1.lock_row = t2.lock_row
WHERE t1.lock_pid = 1000
AND t2.lock_pid != 1000
AND t2.lock_db = 'preg'
AND t2.lock_table = 'mbrs'
LIMIT 1;" | mysql -E test
# I try to find the 4th row which duplicates lock_row (value 2) for
# a certain value of lock_db and lock_table but a different lock_pid
EXPLAIN gives:
*************************** 1. row ***************************
table: t1
type: ref
possible_keys: lockone,locktwo
key: lockone
key_len: 4
ref: const
rows: 2
Extra: Using where; Using index
*************************** 2. row ***************************
table: t2
type: ALL
possible_keys: locktwo
key: NULL
key_len: NULL
ref: NULL
rows: 5
Extra: Using where
This looks quite good for t1 but does not use any index for t2.
How should I set up my index to improve this situation?
Any help is appreciated.
Thomas Spahni
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
