Sorry I missed the explain part.
You are doing a full table scan on the Updates table. There really is
no way around speeding up reading a million rows. That's going to be
dependent mainly on the speed of your disks and then how much memory
you have.
MySQL is optimizing your query to select from the Updates table
instead, so you may want to change your query to match what MySQL is
doing. Not sure if it would be faster, but it's something to try.
Select Count(*) From Update Inner Join Data ON Data.Hash =
Updates.Hash AND Data.Year = Updates.Year
As Alexey mentioned, you should absolutely add a compound index on
Hash+Year.
The bottom line is that you are searching Data for over a million
matching Updates. Without fast disks and/or lots of RAM for caching,
you're not going to get great performance. You just have a lot of
matches to sift through.
On Oct 25, 2005, at 10:32 AM, C.R. Vegelin wrote:
Hi Brent,
Well I did post an EXPLAIN before my query,
but it got squeezed at the end of my former email.
The EXPLAIN for my query says:
==================
id: 1
select-type: simple
table: Updates
type: ALL
possible_keys: Hash, Year
key: NULL
key_len: NULL
ref: NULL
rows: 1003823
Extra: ===================
id: 1
select-type: simple
table: Data
type: ref
possible_keys: Hash, Year
key: Hash
key_len: 8
ref: Updates.Hash
rows: 2
Extra: using where
=====================
And yes, both columns Hash and Year and indexed in both tables.
Regards, Cor
----- Original Message ----- From: "Brent Baisley"
<[EMAIL PROTECTED]>
To: "C.R.Vegelin" <[EMAIL PROTECTED]>
Cc: <mysql@lists.mysql.com>
Sent: Tuesday, October 25, 2005 4:15 PM
Subject: Re: how to increase query speed ?
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/mysql?
[EMAIL PROTECTED]
--
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]
