> i have a speed problem ;). > > table: > title_id bigint(50),title char(200), cdate datetime
Next time, please provide information about existing indices and provide output of EXPLAIN of the query... > query: > select title, max(cdate) as mdt, count(title_id) as num > from entry > where (date_format(cdate, '%Y-%m-%d %H:%i:%s') > between '2004-05-07 00:00:01' AND '2004-05-08 23:59:59') Here MySQL will calculate the date format for each row that it has to check! Rewrite this with the column name not used as parameter for a function: WHERE cdate BETWEEN xxxxxx AND xxxxx This way MySQL can compare the column to constant values, which is much faster! ( "WHERE column > constant + INTERVAL 1 MONTH" proved to be 100 times (!) faster than "WHERE column - INTERVAL 1 MONTH > constant" in one case I came across) > and > (on = 'Y') What is "on"? There is no column in the table with that name! > group by title If you have a combined index on cdate, on and title (or at least part of title) it will probably be faster too. > order by mdt desc > limit 0,25 DESC is usually slower than ASC, but in this case there is no alternative IMHO. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
