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"
> <brent@stripped>
> To: "C.R.Vegelin" <vegelin@stripped>
> Cc: <mysql@stripped>
> 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?
> unsub=brent@stripped
>
>
>
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
