> I'm using the following query > > SELECT DISTINCT a.addrdsp,a.listdate,a.solddate,a.lpricea,a.sprice > FROM archive a, archive b > WHERE a.status='s' AND a.addrdsp IS NOT NULL > AND a.addrdsp = b.addrdsp AND a.solddate < b.solddate > AND date_add(a.solddate, interval 1 year) > b.solddate > ORDER BY a.addrdsp, a.solddate > LIMIT 200; > > I'm trying to look at about 300,000 rows of property data. I'm > interested in > knowing which properties have been sold 2 or more times within a 1 year > period. > > However, when I run the query, I get 100 Brazeau Cresc SW and 100 > Bridlewood > Road SW in my list of properties--even though they have not had 2 or more > 'sales' within a period of one year. I need to limit those > properties which > only occur once in the table.
Shouldn't you require b.status to be 's' as well? > Also, even though all of the fields are indexed, the query takes up to 12 > minutes to complete! I'm not surprized -- you're forcing MySQL to calculate date_add on every single one of the 300,000 rows. Indexing doesn't help you there. / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
