On Tue, Apr 22, 2008 at 8:41 AM, Bruno B. B. Magalhães <
brunomagalhaes@stripped> wrote:
> Hi everybody,
>
> I am back to this list after a long period away due to work time
> restrictions... I have great news and a few interesting applications that I
> will release to the mysql community very soon, most probably as open source.
>
> But now I have a performance problem with a client of mine, that I was not
> able to solve... The problem is that I have a very large table in terms of
> data, about 7.000.000 financial transactions records, with the following
> table (translated from portuguese):
>
> CREATE TABLE `transactions` (
> `client_id` int(5) unsigned zerofill NOT NULL default '00000',
> `client_unit_id` int(4) unsigned zerofill NOT NULL default '0000',
> `client_property_id` int(6) unsigned zerofill NOT NULL default '000000',
> `transaction_id` int(6) unsigned zerofill NOT NULL default '000000',
> `transaction_account_id` int(3) unsigned zerofill NOT NULL default '000',
> `transaction_classification_id` int(3) unsigned NOT NULL default '0',
> `transaction_category_id` int(4) unsigned zerofill NOT NULL default
> '0000',
> `transaction_complement` varchar(200) NOT NULL,
> `transaction_date` date default NULL,
> `transaction_amount` decimal(16,2) NOT NULL,
> `transaction_parcel` varchar(8) NOT NULL,
> `transaction_nature` varchar(1) NOT NULL
> KEY `transactions_idx_1`
> (`client_id`,`client_unit_id`,`client_property_id`,`transaction_account_id`,
>
>
> `transaction_classification_id`,`transaction_category_id`,`transaction_id`,`transaction_date`,`transaction_nature`)
> ) ENGINE=MyISAM DEFAULT CHARSET=utf8
>
> And most the queries are similar to this one:
>
> SELECT UNIX_TIMESTAMP(transactions.transaction_date) AS date,
> transactions.transaction_complement AS complement,
> transactions.transaction_parcel AS parcel,
> transactions.transaction_amount AS amount,
> transactions.transaction_nature AS nature,
> transactions_categories.transaction_category_description
> AS category_description
> FROM transactions AS transactions
> LEFT JOIN transactions_categories AS transactions_categories
> ON transactions.transaction_category_id =
> transactions_categories.transaction_category_id
> WHERE transactions.client_id = :client
> AND transactions.client_unit_id = :unit
> AND transactions.transaction_date >= :start_date
> AND transactions.transaction_date <= :stop_date
> ORDER BY transactions.transaction_date,
> transactions.transaction_id ASC
>
> So the most important indexes are client_id , client_unit_id ,
> client_property_id , transaction_account_id , transaction_classification_id
> , transaction_category_id , transaction_id , transaction_date ,
> transaction_nature, and most of the time they are called together, 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?
>
> Most of the financials reports today takes about 8 to 12 seconds to be
> generated for one month (course that I have to sum previous months totals to
> give the balance).
>
> Thanks in advance...
>
> Regards,
> Bruno B B Magalh'aes
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
> I would think probably not on the date range. An Phil says an EXPLAIN
would probably be helpful. Particularly of note would be the key_len. I
would image an optimal index would be something like
(`client_id`,`client_unit_id`,`transaction_date`)
If memory serves MySQL does not use a composite index to the right of range
scan.
PS I apologize if this gets sent twice... had an issue with the mail client.
--
Rob Wultsch
wultsch@stripped
wultsch (aim)
