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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
