List:General Discussion« Previous MessageNext Message »
From:Rob Wultsch Date:April 22 2008 8:33pm
Subject:Re: Performance
View as plain text  
On Tue, Apr 22, 2008 at 1:13 PM, "Bruno B. B. Magalhães"
<brunomagalhaes@stripped> 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
> > <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
> >
>
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
wultsch@stripped
wultsch (aim)
Thread
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