I am currently trying to optimize the indexes / keys for the MySQL
schema of phpOpenTracker.
I'm wondering if multiple keys, like
CREATE TABLE pot_accesslog (
accesslog_id int(10) unsigned NOT NULL,
client_id int(10) unsigned NOT NULL,
document_id int(11) NOT NULL,
timestamp int(10) unsigned NOT NULL,
PRIMARY KEY (accesslog_id, client_id, document_id, timestamp)
);
would be better than single keys for each column, like
CREATE TABLE pot_accesslog (
accesslog_id int(10) unsigned NOT NULL,
client_id int(10) unsigned NOT NULL,
document_id int(11) NOT NULL,
timestamp int(10) unsigned NOT NULL,
KEY accesslog_id (accesslog_id),
KEY client_id (client_id),
KEY document_id (document_id),
KEY timestamp (timestamp)
);
Have a look at
http://www.sebastian-bergmann.de/single_keys.htm
for a version of the schema that uses single keys, together with
the output of EXPLAIN(query) for some of phpOpenTracker most commonly
used queries.
Have a look at
http://www.sebastian-bergmann.de/multi_keys.htm
for a version of the schema that uses multiple keys, together with
the output of EXPLAIN(query) for some of phpOpenTracker most commonly
used queries.
It'd be really great if some kind soul could have a look at this and
tell me what indexing strategy is suited best for this problem.
Thanks & Greetings,
Sebastian
--
Sebastian Bergmann
http://sebastian-bergmann.de/ http://phpOpenTracker.de/
Did I help you? Consider a gift: http://wishlist.sebastian-bergmann.de/
---------------------------------------------------------------------
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
