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 _______________________________________________ vox-tech mailing list [EMAIL PROTECTED] http://lists.lugod.org/mailman/listinfo/vox-tech