Re: Verifing Data Integrity

I did not mean just to limit by a single column.

the following would verify a hash of several(all) numeric columns:

select sum(col1 + col2 + col3 + col4.........) as hash order by col1; [limit 10000]

On my server the above sql runs in much less than 10 sec for 10000 rows. Obviously it needs to access the table rows.

Cheers

Bob
PS. Perhaps there is another function for alpha data but we find that hashing the numeric data gives us the most critical validation that we need for replica accuracy.

Rick James wrote:
> It will use the index if all the SUMed values are in ONE index. Indexes are
> usually smaller than table. EXPLAIN will say "Using index" to indicate
> such.
>
> (Caveat: This does not apply to the PRIMARY KEY on InnoDB, which is
> effectively the data, itself.)
>
>
>> -----Original Message-----
>> From: bob b [mailto:robertcb-1@comcast.net]
>> Sent: Sunday, July 15, 2007 2:35 AM
>> To: Darpan Dinker
>> Cc: R Bankay; Andrew Brosnan; Paul Sindelar;
>> replication@lists.mysql.com
>> Subject: Re: Verifing Data Integrity
>>
>> What I meant was If you have an index ind1 that has a numeric key
>> say key1 then
>>
>> select sum(key1) from table1 order by key1; (this is a special case
>> and not general )
>>
>> This will likely not access the table and report the sum of key1
>> using only the idex ind1. It is probably not a good idea to sum
>> (primary key) but something else like transaction # which might be
>> indexed.
>>
>> But any numeric item can be summed whether it is indexed or not and
>> will normally scan the entire table.
>>
>>
>>
>>
>>
>> On Jul 15, 2007, at 12:52 AM, Darpan Dinker wrote:
>>
>>
>>> R Bankay wrote:
>>>
>>>> To thoroughly verify data in a table slave/master, first stop the
>>>> db applications.
>>>>
>>>> Then sum any numeric columns (I prefer a floating columns.) e.g.,
>>>>
>>>> select sum(columnfp1), sum(colmnfp2) from table1;
>>>>
>>>> Do this on both master and slave. Should point out that if you
>>>> choose an index with numeric value it will be much faster.
>>>>
>>>>
>>> When you have to scan the whole table, why will using the index
>>> will be faster?
>>>
>>>> Bob B
>>>>
>>>> ----- Original Message ----
>>>> From: Andrew Brosnan <andrew@broscom.com>
>>>> To: Paul Sindelar <paul.lists@mnwebhost.net>
>>>> Cc: replication@lists.mysql.com
>>>> Sent: Saturday, July 14, 2007 6:14:55 PM
>>>> Subject: Re: Verifing Data Integrity
>>>>
>>>> On 7/14/07 at 12:18 PM, paul.lists@mnwebhost.net (Paul Sindelar)
>>>> wrote:
>>>>
>>>>
>>>>
>>>>> Does anyone know a reliable/efficient way of thoroughly
>>>>>
>> verifying
>>
>>>>> the integrity of a slave?
>>>>>
>>>>>
>>>> Would this be done on a routine basis? I think it's more
>>>>
>> realistic
>>
>>>> to do
>>>> routine sanity checks - perhaps you already do. What I do
>>>>
>> is check
>>
>>>> for
>>>> same number of records on master and slave, thoroughly verify
>>>> *some* of
>>>> the data by checking random rows, compare file sizes, etc. I can't
>>>> imagine going through bit by bit without suspecting a problem. I
>>>> think
>>>> I'd be more likely to reset the slave with a new snapshot of the
>>>> masters
>>>> data. :-)
>>>>
>>>> Regards,
>>>> Andrew
>>>>
>>>>
>>>> --
>>>> MySQL Replication Mailing List
>>>> For list archives: http://lists.mysql.com/replication
>>>> To unsubscribe: http://lists.mysql.com/replication?
>>>> unsub=robertcb@yahoo.com
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>> _____________________________________________________________________
>>
>>>> _______________
>>>> Park yourself in front of a world of choices in alternative
>>>> vehicles. Visit the Yahoo! Auto Green Center.
>>>> http://autos.yahoo.com/green_center/
>>>>
>>>>
>> Robert Bankay -AST:7731^29u18e3
>> robertcb-1@comcast.net
>>
>>
>>
>>
>>
>
>
>

-- 
MySQL Replication Mailing List
For list archives: 
http://lists.mysql.com/replication
To unsubscribe:    
http://lists.mysql.com/replication?unsub=lists@pantek.com