[thelist] Message Archive - MySQL

[Chris Marsh]

Ed

> > A better way of doing this may be to have a 'flag' in the
> > message table called 'deleted', which can be set to true or 
> 
> Of course your original approach will tend to keep the 
> Messages table short and quickly search-able.
> 
> I'm not sure anyone answered your original question; here's 
> my stab at it.  To use your propose approach, before you do 
> your DELETE, the copy can be accomplished with something like:
> 
>   INSERT INTO message_archive 
>   SELECT * FROM messages
>   WHERE touid = '12345' AND (TO_DAYS(Now()) - TO_DAYS(date)) > 30;
> 
> (MS Access syntax, YMMV.) 

Thanks for that. I reached this conclusion slightly ahead of your email.
The only difference is that I specified the column names in both the
insert and select statements (not sure if this is required in MySQL but
I did it anyway) and I used I used LOW_PRIORITY. I'm not sure if this
did me any favours at all, but if anyone has any comment on the use of
LOW_PRIORITY I would welcome it. I then deleted the records with a
second trip to the database. This doesn't feel as if it is as efficient
as it could be, but it suffices.

I'm increasing my basic knowledge of SQL more and more as I have had to
do more and more DB stuff recently; and I'm quite enjoying it. Thanks
all who have contributed to my education.

Regards

Chris Marsh