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
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
