How about posting the explain for your query. Just put explain before
it, MySQL with then tell you how it will go about executing the
query, like which indexes it's using. I assume you have both columns
indexed?
On Oct 25, 2005, at 4:46 AM, C.R. Vegelin wrote:
Hi List,
I have a performance problem I can't get solved.
I have 2 tables, called Updates (1 mln rows) and Data (5 mln rows).
Table Updates has 2 (non-unique) keys, defined as:
Hash bigint(20) unsigned default NULL
Year tinyint(4) NOT NULL default '0'
Table Data has the same 2 (non-unique) keys, defined as:
Hash bigint(20) unsigned NOT NULL default '0'
Year tinyint(4) NOT NULL default '0'
The final purpose is to insert and update Data with new / changed
Updates.
Before doing so, i have a "simple" query, like:
Select Count(*) From Data Inner Join Updates ON Data.Hash =
Updates.Hash AND Data.Year = Updates.Year;
but this one takes more than 30 minutes.
Before running this query, I did: Analyze Table Updates, Data; to
speed up acc. the manual 7.2.3.
Does someone know how to increase the performance for this query ?
Below the results of the explain for this query.
TIA, regards Cor
myQuery id select_type table type possible_keys key key_len ref
rows Extra
1 SIMPLE Updates ALL Hash,Year
1003823
1 SIMPLE Data ref Hash,Year Hash 8 Updates.Hash 2 Using where
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
