[vox-tech] mysql 4 questions

[Dylan Beaudette]

Hi everyone,

I am working with a rather large mysql database, and having some rather
disapointing results in terms of query times....

some background:

Machine info:
Pentium 4 CPU 2.66GHz
512Mb RAM
database on SCSI RAID

DB info:
For now all of the slow queries are operating on a single, static table
called 'UDC'. This is a MERGE table that is composed of 12 tables:
(UDC_1990, UDC_1991, ..., UDC_2002)

There is a total of 42695935 records in the entire UDC MERGE table.

Here is an example query that is taking far longer that it did on an older
oracle 8i machine.:

EXPLAIN SELECT [a bunch of columns]
   FROM UDC
   WHERE UDC.year IN ('2000') AND county_cd IN ('28') AND UDC.chem_code IN
('198');

+---------------------------------+-----------+---------+------
| possible_keys                   | key       | key_len | ref
+---------------------------------+-----------+---------+------
| CHEM_CODE,COUNTY_CD,COUNTY_CD_2 | COUNTY_CD |       3 | NULL
+---------------------------------+-----------+---------+------

-+--------+-------------+
 | rows   | Extra       |
-+--------+-------------+
 | 354700 | Using where |
-+--------+-------------+

The UDC table (and all of its components) has/have the following index
structure:

  KEY `UDC_CHEM_NDX` (`CHEM_CODE`),
  KEY `UDC_COMTRS_NDX` (`COMTRS`),
  KEY `UDC_COUNTY_NDX` (`COUNTY_CD`),
  KEY `UDC_PRODNO_NDX` (`PRODNO`),
  KEY `UDC_SITE_CHEM_NDX` (`SITE_CODE`,`CHEM_CODE`),
  KEY `UDC_SITE_NDX` (`SITE_CODE`),
  KEY `UDC_USE_NDX` (`USE_NO`),
  KEY `COUNTY_CD` (`COUNTY_CD`,`YEAR`)

Perhaps I am a little confused, but after reading some of the MySQL docs
it would seem that I would need a mulitple column index on each column in
my
WHERE clause. in this case i am using 3 columns: (YEAR, COUNTY_CD,
CHEM_CODE ) ... so would this query only benifit from an index defined as
:
KEY `COUNTY_CD` (`YEAR`, `COUNTY_CD`,`CHEM_CODE`)...?

any ideas?

thanks!

Dylan