l i n u x - u s e r s - g r o u p - o f - d a v i s
L U G O D
 
Next Meeting:
April 21: Google Glass
Next Installfest:
TBD
Latest News:
Mar. 18: Google Glass at LUGOD's April meeting
Page last updated:
2004 Sep 10 17:28

The following is an archive of a post made to our 'vox-tech mailing list' by one of its subscribers.

Report this post as spam:

(Enter your email address)
[vox-tech] mysql 4 questions
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

[vox-tech] mysql 4 questions



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
vox-tech@lists.lugod.org
http://lists.lugod.org/mailman/listinfo/vox-tech



LinkedIn
LUGOD Group on LinkedIn
Sign up for LUGOD event announcements
Your email address:
facebook
LUGOD Group on Facebook
'Like' LUGOD on Facebook:

Hosting provided by:
Sunset Systems
Sunset Systems offers preconfigured Linux systems, remote system administration and custom software development.

LUGOD: Linux Users' Group of Davis
PO Box 2082, Davis, CA 95617
Contact Us

LUGOD is a 501(c)7 non-profit organization
based in Davis, California
and serving the Sacramento area.
"Linux" is a trademark of Linus Torvalds.

Sponsored in part by:
Sunset Systems
Who graciously hosts our website & mailing lists!