On Tue, Apr 22, 2008 at 1:13 PM, "Bruno B. B. Magalhães"
> 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
> 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
> > > 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:
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...)