List:General Discussion« Previous MessageNext Message »
From:Phil Date:April 22 2008 4:01pm
Subject:Re: Performance
View as plain text  
I'm sure if you created an index on
client_id,client_unit_id,transaction_date (with optionally something else to
make unique) it would increase performance.

What does an EXPLAIN give you?

Phil

On Tue, Apr 22, 2008 at 11: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
>



-- 
Help build our city at http://free-dc.myminicity.com !

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