Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Aug 2011
    Posts
    8

    Unanswered: my query very slow very slow

    hello friends,

    am need help. i have 2 tables one is account and other is transation


    account table have 868 rows. and fetched in 0.0.925s

    CREATE TABLE `tblaccounts` (
    `ID` int(11) NOT NULL DEFAULT '0',
    `AcTitle` varchar(70) CHARACTER SET utf8 DEFAULT NULL,
    `Type` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
    `Address` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
    `City` varchar(25) CHARACTER SET utf8 DEFAULT NULL,
    `Climit` decimal(19,4) DEFAULT '0.0000',
    `Type1` tinyint(4) DEFAULT '0',
    `Type2` tinyint(4) DEFAULT '0',
    `obalance` decimal(19,4) DEFAULT '0.0000',
    `contact` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
    `cRate` decimal(19,4) DEFAULT '0.0000',
    `rcRate` decimal(19,4) DEFAULT '0.0000',
    `PhoneNo` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
    `MobileNo` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
    `BankAccount1` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
    `BankAccount2` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
    `miscInfo1` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
    `miscInfo2` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
    `miscInfo3` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
    `miscInfo4` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
    `l_charges` decimal(19,4) DEFAULT NULL,
    `m_charges` decimal(19,4) DEFAULT NULL,
    PRIMARY KEY (`ID`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


    and transationac table 152910 rows fetched in 1.4129s.

    CREATE TABLE `tbltransactionac` (
    `ID` int(11) NOT NULL,
    `eDate` date DEFAULT NULL,
    `DocNo` int(11) DEFAULT '0',
    `Type` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
    `AccID` int(11) DEFAULT '0',
    `PayDesc` varchar(200) CHARACTER SET utf8 NOT NULL,
    `Debit` decimal(19,2) DEFAULT '0.00',
    `Credit` decimal(19,2) DEFAULT '0.00',
    `EntryGroup` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
    PRIMARY KEY (`ID`),
    KEY `ttrans_ix_accid_edate_debit_credit` (`AccID`,`eDate`,`Debit`,`Credit`),
    CONSTRAINT `AccID` FOREIGN KEY (`AccID`) REFERENCES `tblaccounts` (`ID`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


    my problem is
    my query when am join both tables.
    it's take 184.4957s and get 152910

    select tbltransactionac.eDate AS eDate,tbltransactionac.DocNo AS DocNo,tbltransactionac.Type AS Type,tbltransactionac.PayDesc AS PayDesc,ifnull(tbltransactionac.Debit,0) AS Debit,ifnull(tbltransactionac.Credit,0) AS Credit,tblaccounts.ID AS ID,tblaccounts.AcTitle AS AcTitle,tblaccounts.Type AS accType,tblaccounts.Address AS Address,tblaccounts.City AS City,tbltransactionac.ID AS tid from (tblaccounts join tbltransactionac on((tblaccounts.ID = tbltransactionac.AccID))) order by tbltransactionac.eDate;

    can someone help me plz
    why speed is slow ???
    what am do now ??

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please do an EXPLAIN on your query for us
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2011
    Posts
    8
    thanks for replay

    1. in account table have customer record
    2. in transactionac table have daily A/C cash transaction
    3. in query am want get customer current account balance

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, that's not what an EXPLAIN is

    run this query and show the output --
    Code:
    EXPLAIN select tbltransactionac.eDate AS eDate,tbltransactionac.DocNo AS DocNo,tbltransactionac.Type AS Type,tbltransactionac.PayDesc AS PayDesc,ifnull(tbltransactionac.Debit,0) AS Debit,ifnull(tbltransactionac.Credit,0) AS Credit,tblaccounts.ID AS ID,tblaccounts.AcTitle AS AcTitle,tblaccounts.Type AS accType,tblaccounts.Address AS Address,tblaccounts.City AS City,tbltransactionac.ID AS tid from (tblaccounts join tbltransactionac on((tblaccounts.ID = tbltransactionac.AccID))) order by tbltransactionac.eDate;
    please make sure you put [code]...[/code] tags around the results, so that you keep the formatting
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2011
    Posts
    8
    SORRY ,


    id select_type table type possible_keys key key_len ref rows Extra
    1 SIMPLE tblaccounts ALL PRIMARY 1036 Using temporary; Using filesort
    1 SIMPLE tbltransactionac ref ttrans_ix_accid_edate_debit_credit ttrans_ix_accid_edate_debit_credit 5 dbumer.tblaccounts.ID 83 Using where

    +----+-------------+------------------+------+----------------------------------
    --+------------------------------------+---------+-----------------------+------
    +---------------------------------+
    | id | select_type | table | type | possible_keys
    | key | key_len | ref | rows
    | Extra |
    +----+-------------+------------------+------+----------------------------------
    --+------------------------------------+---------+-----------------------+------
    +---------------------------------+
    | 1 | SIMPLE | tblaccounts | ALL | PRIMARY
    | NULL | NULL | NULL | 1036
    | Using temporary; Using filesort |
    | 1 | SIMPLE | tbltransactionac | ref | ttrans_ix_accid_edate_debit_credi
    t | ttrans_ix_accid_edate_debit_credit | 5 | dbumer.tblaccounts.ID | 83
    | Using where |
    +----+-------------+------------------+------+----------------------------------
    --+------------------------------------+---------+-----------------------+------
    +---------------------------------+
    2 rows in set (0.00 sec)



    I DON'T KNOW HOW TO PAST IT

  6. #6
    Join Date
    Aug 2011
    Posts
    8
    id select_type table type possible_keys key key_len ref rows Extra
    1 SIMPLE tblaccounts ALL PRIMARY 1036 Using temporary; Using filesort
    1 SIMPLE tbltransactionac ref T* T* 5 tblaccounts.ID 83 Using where

    T=ttrans_ix_accid_edate_debit_credit

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you forgot the [code]...[/code] tags around the results
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    like this --
    Code:
    +----+-------------+------------------+------+------------------------------------+------------------------------------+---------+-----------------------+------+---------------------------------+
    | id | select_type | table            | type | possible_keys                      | key                                | key_len | ref                   | rows | Extra                           |
    +----+-------------+------------------+------+------------------------------------+------------------------------------+---------+-----------------------+------+---------------------------------+
    |  1 | SIMPLE      | tblaccounts      | ALL  | PRIMARY                            | NULL                               | NULL    | NULL                  | 1036 | Using temporary; Using filesort |
    |  1 | SIMPLE      | tbltransactionac | ref  | ttrans_ix_accid_edate_debit_credit | ttrans_ix_accid_edate_debit_credit | 5       | dbumer.tblaccounts.ID |   83 | Using where                     |
    +----+-------------+------------------+------+------------------------------------+------------------------------------+---------+-----------------------+------+---------------------------------+
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the first line is telling you that it is not using an index on the accounts table, and this is because you apparently want all accounts, so that makes sense

    there are 1036 rows, not 868

    the second line is telling you that it's using the index ttrans_ix_accid_edate_debit_credit to match transactions to accounts

    that is also as it should be

    wanna know where the slowdown is? it's Using temporary; Using filesort

    this is caused by your ORDER BY clause

    remove that, and see how much the performance improves
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Aug 2011
    Posts
    8
    SORRY, BECAUSE AM NEW ON dBforums
    Code:
    +----+-------------+------------------+------+------------------------------------+------------------------------------+---------+-----------------------+------+---------------------------------+
    | id | select_type | table            | type | possible_keys  | key                                | key_len | ref                   | rows| Extra                           |
    +----+-------------+------------------+------+------------------------------------+------------------------------------+---------+-----------------------+------+---------------------------------+
    |  1 | SIMPLE      | tblaccounts      | ALL  | PRIMARY  	  | NULL                               | NULL    | NULL                  | 1036| Using temporary; Using filesort |
    |  1 | SIMPLE      | tbltransactionac | ref  | ttrans_ix_accid_edate_debit_credit 	  | ttrans_ix_accid_edate_debit_credit | 5       | dbumer.tblaccounts.ID |   83| Using where                     |
    +----+-------------+------------------+------+------------------------------------+------------------------------------+---------+-----------------------+------+---------------------------------+
    2 rows in set (0.00 sec)

  11. #11
    Join Date
    Aug 2011
    Posts
    8
    but when am use SELCT command only Account table like
    Code:
    SELECT * FROM tblaccounts t;
    its return me 868 records

    Code:
    EXPLAIN SELECT * FROM tblaccounts;
    +----+-------------+-------------+------+---------------+------+---------+------+------+-------+
    | id | select_type | table       | type | possible_keys | key  | key_len | ref| rows   | Extra |
    +----+-------------+-------------+------+---------------+------+---------+------+------+-------+
    |  1 | SIMPLE      | tblaccounts | ALL  | NULL          | NULL | NULL    | NULL| 1036  |       |
    +----+-------------+-------------+------+---------------+------+---------+------+------+-------+
    1 row in set (0.00 sec)
    am remove order by but still get 168.2691s

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, i have no idea
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Aug 2011
    Posts
    8
    am use mySQL Administrator
    and run Repair Tables wizard
    use Repair method (Extended , Use FRM)
    now same query featch all rows in 2.4137

    but one problem again
    when am reset mysql it's take much time again
    now can you give me any other idea.
    and after run wizard it's change EXPLAIN
    please see
    Code:
    +----+-------------+------------------+--------+------------------------------------+---------+---------+-------------------------------+--------+----------------+
    | id | select_type | table            | type   | possible_keys    					| key     | key_len | ref                           | rows   | Extra  		  |
    +----+-------------+------------------+--------+------------------------------------+---------+---------+-------------------------------+--------+----------------+
    |  1 | SIMPLE      | tbltransactionac | ALL    | ttrans_ix_accid_edate_debit_credit | NULL    | NULL    | NULL                          | 152715 | Using filesort |
    |  1 | SIMPLE      | tblaccounts      | eq_ref | PRIMARY    						| PRIMARY | 4       | dbumer.tbltransactionac.AccID |      1 |                |
    +----+-------------+------------------+--------+------------------------------------+---------+---------+-------------------------------+--------+----------------+
    2 rows in set (0.00 sec)

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sakamal View Post
    now can you give me any other idea.
    nope, sorry

    there's obviously a lot more going on here than what you've shown

    my advice is to hire a professional mysql database administrator

    (note: not me)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Jun 2007
    Posts
    197
    If you are not going to use huge data Then its better to use MyISAM
    Engine

    MySQL very much depend on server performance also , RAM , allocated
    MySQL variables , load on server,64 bit or 32 bit OS
    Are you Using Linux or Windows

    That is going to be production or Test server
    All above matters

    Regards
    Senior MySQL DBA
    Last edited by ankur02018; 08-26-11 at 10:39.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •