Optimizing MySQL

[Thanos]

Hello all,

can someone advice me some links/infos on how to optimize MySQL server to
execute very complex queries ?
My configuration is as follows : P4 2.8Ghz/512MB running on Fedora Core 1
with latest updates, 2xHDD 36Gb SCSI 10000rpm (RAID 1), MySQL 4.0.22
configured with my-large.cnf, frontend application written in PHP 4.
I need to execute complex queries using multiple joins/unions on large
tables (more than 20,000/30,000 records) and the execution time is really
high (it tooks more than 2 minutes to get results).
mysqld configuration is:

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 392
sort_buffer_size = 2M
read_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size= 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

Any advice ?

Cheers,
A.

[Aggro]

Thanos wrote:

I need to execute complex queries using multiple joins/unions on large
tables (more than 20,000/30,000 records) and the execution time is really
high (it tooks more than 2 minutes to get results).

Do you have indexes in all the columns you are using in the joins and
have you checked that those are used, using EXPLAIN?

[swdev2]

Heya Thanos -
sounds like you kicked the server into TABLE SCAN mode.
Look at your query, any columns in a where clause, a join clause
and go back and index those columns.
Your results time should reduce by 70 percent, from there.
regards [Bill]

--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
Mondo Cool TeleCom -> http://www.efgroup.net/efgcog.html
Mondo Cool WebHosting -> http://www.efgroup.net/efglunar.html
Mondo Cool Satellites -> http://www.efgroup.net/sat
VFP Webhosting? You BET! -> http://efgroup.net/vfpwebhosting
mySql / VFP / MS-SQL

"Thanos" <th************@fastwebnet.it> wrote in message
news:ag*******************@tornado.fastwebnet.it.. .

Hello all,

can someone advice me some links/infos on how to optimize MySQL server to
execute very complex queries ?
My configuration is as follows : P4 2.8Ghz/512MB running on Fedora Core 1
with latest updates, 2xHDD 36Gb SCSI 10000rpm (RAID 1), MySQL 4.0.22
configured with my-large.cnf, frontend application written in PHP 4.
I need to execute complex queries using multiple joins/unions on large
tables (more than 20,000/30,000 records) and the execution time is really
high (it tooks more than 2 minutes to get results).
mysqld configuration is:

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 392
sort_buffer_size = 2M
read_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size= 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

Any advice ?

Cheers,
A.