Help me optimize my query

[Maximo Migliari]

Im running the following query:

SELECT
user.nickname,
user.id,
user_detail.points
FROM
user,
user_detail
WHERE
user.details = user_detail.id
AND user.id > 101
AND user.language = 'en'
ORDER BY user_detail.points DESC
LIMIT 5;

It basically lets me know the top 5 contributors to my website.

Table user has: 32806 records, takes up 11.5 MB, and is of type InnoDB
table user_detail has: 32838 records, takes up 5.4 MB, and is of type InnoDB

the entire database takes up about 190 MB. Its a mix between MyISAM and InnoDB tables
(InnoDB for the tables that are often updated/inserted and MyISAM for the tables that are mainly selected from).

Its running on a dedicated PIII-866 Mhz server with 512 MB of RAM, and a 18 GIG 10,000 RPM SCSI drive.
The server is running MySQL and Apache. MySQL averages at about 22 queries a second, and the load is always between 0.7 and 2
there are always about 50 httpd processes and about 60 mysqld processes. My website burns up about 35GB of bandwidth a month, and I get arround 3 million pageviews a month.

The query takes on average 0.8 seconds to run on the MySQL client - but is often saved in my hostname-slow.log file as a slow query.

This is an extract of the my.cnf:

skip-locking
set-variable = key_buffer=192M
set-variable = max_allowed_packet=1M
set-variable = table_cache=20000
set-variable = record_buffer=1M
set-variable = sort_buffer=1M
set-variable = net_buffer_length=16K
set-variable = myisam_sort_buffer_size=64M
set-variable = thread_cache=8
set-variable = max_connections=10000
log-bin
server-id = 1
innodb_data_file_path = innodb/ibdata1:1000M:autoextend
set-variable = innodb_mirrored_log_groups=1
set-variable = innodb_log_files_in_group=3
set-variable = innodb_log_file_size=32M
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1
innodb_log_archive=0
set-variable = innodb_buffer_pool_size=192M
set-variable = innodb_additional_mem_pool_size=64M
set-variable = innodb_file_io_threads=4
set-variable = innodb_lock_wait_timeout=50


I don't really know how LEFT JOINS, INNER JOINS and so on work, but I want to increase the performance of the query.
This is the result of the EXPLAIN for the query:

+-------------+-------+-----------------------------+---------+---------+----------------+-------+-----------------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------------+-------+-----------------------------+---------+---------+----------------+-------+-----------------------------------------+
| user_detail | range | PRIMARY,id,points | points | 4 | NULL | 15567 | where used; Using index; Using filesort |
| user | ref | PRIMARY,id,details,language | details | 4 | user_detail.id | 1 | where used |
+-------------+-------+-----------------------------+---------+---------+----------------+-------+-----------------------------------------+

Someone told me that Using filesort is not a good sign... how can I improve the performace of this query? Any other tuning suggestions?
Im using InnoDB just because its fast for updates/inserts, but none of the update/insert queries are using transactions.

Thanks a bunch!
Maximo Migliari.



---------------------------------------------------------------------
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