Hi, On Fri, 2002-02-22 at 11:29, [EMAIL PROTECTED] wrote: > Have u encountered this kind of query? > > mysql> select edate from traffic where year(edate)='2001' group by > month(edate); > > +------------+ > | edate | > +------------+ > | 2001-04-29 | > | 2001-05-01 | > | 2001-06-01 | > | 2001-07-01 | > | 2001-08-01 | > | 2001-09-01 | > | 2001-10-30 | > | 2001-11-01 | > | 2001-12-01 | > +------------+ > 9 rows in set (10 min 53.83 sec) > > mysql> select count(*) from traffic; > +----------+ > | count(*) | > +----------+ > | 2645563 | > +----------+ > 1 row in set (0.00 sec) > > mysql> > > is there a problem with my select statement?...
Well, you make both the WHERE as well as the GROUP statement work on the result of an expression YEAR(...) MONTH(...) rather than a constant (like a plain column). Also, since you use an expression in your WHERE, the server can't use an index on edate even if there is one. So, to process your query, the server has to do the following: - Do a full table scan, calculating the YEAR() from every edate, checking it for the right year... - Once all that is collected, it has to go through all of the result set, calculating the MONTH() from every edate, and only THEN can it start grouping and summarising. No wonder the query takes a while.... What type is 'edate' exactly? If it's just a DATE field, one idea could be to store the DAY/MONTH/YEAR bits as separate columns. Then, if you index the YEAR field, that index can be used to quickly find all the rows within the right year. No expression evaluation necessary. As someone else mentioned, it might also be a good idea to simply have a separate table for each year. You can always use a MERGE table if you need to do queries that span over multiple years. Anyway, with the MONTH being a separate column, the GROUP statement no longer has to evaluate an expression. So there too will it be much faster. These are just a few basic ideas... issues such as these (and much more) are covered in MySQL training courses: http://www.mysql.com/training/. Regards, Arjen. -- MySQL Training in Brisbane: 18-22 March, http://www.mysql.com/training/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com --------------------------------------------------------------------- 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
