On Tue, Apr 22, 2008 at 1:13 PM, "Bruno B. B. Magalhães" <[EMAIL PROTECTED]> wrote:
> Hi Phill, Rob and Perrin, > > I forgot to attach the explain query from MySQL, course it's one of the most > important things... Sorry!!! > > EXPLAIN SELECT UNIX_TIMESTAMP(transactions.transaction_date) AS date, > > transactions.transaction_complement AS complement, > transactions.transaction_value AS value, > > transactions.transaction_nature AS nature, > > transactions_categories.transaction_category_description AS > category_description, > clients_units.client_unit_complement AS > unit_complement > > FROM transactions AS transactions > LEFT JOIN transactions_categories AS transactions_categories > ON transactions.transaction_category_id = > transactions_categories.transaction_category_id > LEFT JOIN clients_units AS clients_units > ON transactions.client_id = clients_units.client_id > AND transactions.client_unit_id = > clients_units.client_unit_id > WHERE transactions.client_id = '00379' > AND transactions.transaction_account_id = '001' > AND transactions.transaction_classification_id = '101' > AND transactions.transaction_date >= '2008-03-01' > AND transactions.transaction_date <= '2008-03-31' > ORDER BY transactions.transaction_date ASC > > > 1 SIMPLE transactions ref transactions_idx_1 > transactions_idx_1 4 const 9582 Using where; Using temporary; > Using filesort > 1 SIMPLE transactions_classifications ALL NULL NULL NULL > NULL 1660 > 1 SIMPLE clients_units ref clients_units_idx_1 > clients_units_idx_1 8 > bap_sat.transactions.client_id,bap_sat.transactions.client_unit_id 1 > > Seems that the transactions table is the sort of the problem, as it's using > file sort and where... But my myisam sort cache is big, I thing it's about > 80MB or so... > > Thank you everybody for your help!!! > > Best Regards, > Bruno B B Magalhaes > > > > > > On Apr 22, 2008, at 3:21 PM, Perrin Harkins wrote: > > > > > > > > > > On Tue, Apr 22, 2008 at 11:41 AM, Bruno B. B. Magalhães > > <[EMAIL PROTECTED]> wrote: > > > > > I thing > > > the most problematic part of those queries are the date range part, > > > should I > > > use a different index only for this column to maintain the index small? > > > > > > > My experience with doing data warehousing in MySQL was that when all > > you need is day granularity, you are much better off having a > > de-normalized 'days_since_epoch' column or a date dimension table with > > a column like that. Then your date math becomes simple integer > > comparisons which are much faster. > > > > - Perrin > > > About how many rows actually get returned? If it is significantly less than 9582 you are not well indexed for the query. If you getting not all that many results returned then the filesort would not significantly impact performance. I think the giant composite index is not being used well from the length returned in the explain. I would try a new index on something like: client_id transaction_account_id transaction_classification_id transaction_date 1 SIMPLE transactions_classifications ALL NULL NULL NULL NULL 1660 That sucks. I am not seeing that table in query you sent (may be going blind...) -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
