List:General Discussion« Previous MessageNext Message »
From:Bruno B. B. Magalhães Date:April 22 2008 8:13pm
Subject:Re: Performance
View as plain text  
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  
			   clients_units.client_unit_complement AS unit_complement
		  FROM transactions AS transactions
	 LEFT JOIN transactions_categories AS transactions_categories
			ON transactions.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
> <brunomagalhaes@stripped> 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
> --
> MySQL General Mailing List
> For list archives:
> To unsubscribe:

auto_incrementHiep Nguyen21 Apr
  • Re: auto_incrementSebastian Mendel22 Apr
    • Re: auto_incrementSebastian Mendel22 Apr
      • Re: auto_incrementHiep Nguyen22 Apr
  • Re: auto_incrementBen Clewett22 Apr
    • Re: auto_incrementSebastian Mendel22 Apr
      • Re: auto_incrementBen Clewett22 Apr
      • PerformanceBruno B .  B .  Magalhães22 Apr
        • Re: PerformancePhil22 Apr
        • Re: PerformanceRob Wultsch22 Apr
        • Re: PerformanceRob Wultsch22 Apr
        • Re: PerformancePerrin Harkins22 Apr
          • Re: PerformanceBruno B .  B .  Magalhães22 Apr
            • Re: PerformanceRob Wultsch22 Apr