Sebastian Bergmann wrote:
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
--------------------------------------------------------------------- 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
