| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
|
08-03-11, 12:03
|
|
Registered User
|
|
Join Date: Aug 2011
Posts: 7
|
|
|
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 ??
|
|
08-03-11, 12:11
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
please do an EXPLAIN on your query for us
|
|
08-03-11, 12:17
|
|
Registered User
|
|
Join Date: Aug 2011
Posts: 7
|
|
|
|
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
|
|
08-03-11, 12:30
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
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
|
|
08-03-11, 12:39
|
|
Registered User
|
|
Join Date: Aug 2011
Posts: 7
|
|
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
|
|
08-03-11, 12:43
|
|
Registered User
|
|
Join Date: Aug 2011
Posts: 7
|
|
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
|
|
08-03-11, 12:44
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
you forgot the [code]...[/code] tags around the results
|
|
08-03-11, 12:46
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
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 |
+----+-------------+------------------+------+------------------------------------+------------------------------------+---------+-----------------------+------+---------------------------------+
|
|
08-03-11, 12:50
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
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
|
|
08-03-11, 12:51
|
|
Registered User
|
|
Join Date: Aug 2011
Posts: 7
|
|
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)
|
|
08-03-11, 13:01
|
|
Registered User
|
|
Join Date: Aug 2011
Posts: 7
|
|
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
|
|
08-03-11, 13:13
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
|
|
08-03-11, 15:02
|
|
Registered User
|
|
Join Date: Aug 2011
Posts: 7
|
|
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)
|
|
08-03-11, 15:23
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
Quote:
Originally Posted by sakamal
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)
|
|
08-26-11, 09:03
|
|
Registered User
|
|
Join Date: Jun 2007
Posts: 189
|
|
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 09:39.
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
my query very slow very slow
