For this query
mysql> EXPLAIN
-> SELECT COUNT(accesslog.document_id) AS item_count,
-> data_table.string AS item
->
-> FROM pot_accesslog accesslog,
-> pot_visitors visitors,
-> pot_documents data_table
->
-> WHERE accesslog.accesslog_id = visitors.accesslog_id
-> AND accesslog.client_id = 1
-> AND accesslog.document_id = data_table.data_id
-> GROUP BY accesslog.document_id,
-> data_table.string
-> ORDER BY item_count DESC;
+------------+--------+--------------------------------------+---------+---------+-----------------------+------+----------------------------------------------+
| table | type | possible_keys | key | key_len |
ref | rows | Extra |
+------------+--------+--------------------------------------+---------+---------+-----------------------+------+----------------------------------------------+
| accesslog | ALL | accesslog_id,client_time,document_id | NULL | NULL |
NULL | 6 | Using where; Using temporary; Using filesort |
| visitors | index | PRIMARY | PRIMARY | 4 |
NULL | 3 | Using where; Using index |
| data_table | eq_ref | PRIMARY | PRIMARY | 4 |
accesslog.document_id | 1 | |
+------------+--------+--------------------------------------+---------+---------+-----------------------+------+----------------------------------------------+
3 rows in set (0.01 sec)
none of the possible indexes of the pot_accesslog table
CREATE TABLE pot_accesslog (
accesslog_id int(11) NOT NULL,
client_id int(10) unsigned NOT NULL,
timestamp int(10) unsigned NOT NULL,
document_id int(11) NOT NULL,
exit_target_id int(11) DEFAULT '0' NOT NULL,
entry_document enum('0','1') NOT NULL,
KEY accesslog_id (accesslog_id),
KEY client_time (client_id, timestamp),
KEY document_id (document_id)
) DELAY_KEY_WRITE=1;
is used.
Any help on improving the performance of this would be appreciated,
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
