Performance Problems with Huge Table

[Carlos Fernando Scheidecker Antunes]

I am having performance problems with my server with larger tables.

I Have a 512MB Pentium III Red Hat Linux 7 server running MySQL version 3.23.32 
packaged by Red Hat

This server's /etc/my.cnf is as follows :

[mysqld]
pid-file=/var/run/mysqld/mysqld.pid
datadir=/var/lib/mysql
port=3306
socket=/var/lib/mysql/mysql.sock
set-variable=max_connections=2000
skip-locking
set-variable = key_buffer=384M
set-variable = max_allowed_packet=1M
set-variable = table_cache=512
set-variable = sort_buffer=2M
set-variable = record_buffer=2M
set-variable = thread_cache=8
set-variable = thread_concurrency=8 # Try number of CPU's*2
set-variable = myisam_sort_buffer_size=64M
log-bin
server-id = 1
     
[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

I have a table structure like this, including indexes :

# Table structure for table 'tbl_Parts'
#

CREATE TABLE tbl_Parts (
  Vendor varchar(30) NOT NULL default '',
  PartNumber varchar(20) NOT NULL default '',
  Suplier varchar(20) NOT NULL default '',
  Quantity int(11) default '0',
  Prico float default '0',
  Description varchar(50) NOT NULL default '',
  DateHour datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY (Vendor,PartNumber,Suplier),
  KEY index_data(DateHour),
  KEY index_Company(Suplier,DateHour),
  KEY index_description(Description,Vendor),
  KEY index_Vendor(Vendor)
) TYPE=MyISAM;

As you can see I have also an Index for the Vendor.

The problem is that when I do an statement (that I use pretty much) :

SELECT DISTINCT(Vendor) from tbl_Parts order by Vendor;

It takes up to 52 seconds to return it since my table tbl_Parts has 1.130.300 records.

This SQL statement is always ran with a PHP script so that the user can Select the 
vendor and type the PartNumber he/she is looking for.

Based on my config, structure and situation, is there anyone who could kindly help me 
on boost its performance? 52 seconds to return the SELECT DISTINCT statement is very 
long.

By the way, my system has an SCSI HD which is quite fast for it.

Thank you all,

Carlos Fernando Scheidecker Antunes.