I would suggest creating a new table to hold vendor information. Then remove
the varchar vendor field in the parts table and replace it with an integer
that represents the vendorid from the vendor table you just created. This
should speed things up consideribly. You can do a left join any time you
want info from the vendor table included in queries involving the parts
table.
ryan
----- Original Message -----
From: "Carlos Fernando Scheidecker Antunes" <[EMAIL PROTECTED]>
To: "MySQL List" <[EMAIL PROTECTED]>
Sent: Saturday, May 05, 2001 4:53 PM
Subject: Performance Problems with Huge Table
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.
---------------------------------------------------------------------
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
