Hi! I wonder what would be faster. Let's say I want to run two kinds of
SQL queries on the table:
SELECT id FROM mytable WHERE key1=? AND key2=? AND key3=?;
DELETE FROM mytable WHERE key1=? AND key2=? AND id=?;
What's better? This:
CREATE TABLE mytable (
key1 CHAR(20) NOT NULL,
key2 CHAR(20) NOT NULL,
key3 CHAR(100) NOT NULL,
id INT UNSIGNED NOT NULL,
UNIQUE KEY (key1,key2,key3),
INDEX (key1,key2,id)
);
Or this:
CREATE TABLE mytable (
key1 CHAR(20) NOT NULL,
key2 CHAR(20) NOT NULL,
key3 CHAR(100) NOT NULL,
id INT UNSIGNED NOT NULL,
INDEX (key1,key2),
INDEX (key1,key2),
INDEX (key3),
INDEX (id)
);
Or even this:
CREATE TABLE mytable (
key1 CHAR(20) NOT NULL,
key2 CHAR(20) NOT NULL,
key3 CHAR(100) NOT NULL,
id INT UNSIGNED NOT NULL,
INDEX (key1),
INDEX (key2),
INDEX (key3),
INDEX (id)
);
First I thought that the first is most efficient, but then I noticed
that it would create keys of 140 bytes in length. And most of the time
values of keys would be much shorter then limits, so may be separate
keys would work faster? And take less space in memory - be better
cached?
I will run some tests on my own now, but if anybody have any suggestions
I'd really appreciate them.. Thanx.
Andrew.
---------------------------------------------------------------------
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
