Hi,
If I look under task manager-> Processes, mysqld-max-nt.e uses up 99%. For
simple queries, it seems to be fast, but for long and complex query, it
just hangs.
An example of aa query we're trying to run:
SELECT ADV.AMB_No AS ColCode, CD.Company_Name AS ColDesc, ADV.Account_Item_ID AS
RowCode, AI.Account_Item AS RowDesc,
ADV.Value AS Amount, ER.Exchange_Rate AS DefRate, UER.Exchange_Rate AS UseRate ,
AI.Is_Ratio, AI.Ratio_Calculation,
ADV.Year_End, TIL.Line_No, TIL.Hierarchy_Level, TIL.SubTotal_ID, AI.Negative_Polarity
FROM (((((Account_Data_Values AS ADV INNER JOIN Account_Items AS AI ON
ADV.Account_Item_ID = AI.Account_Item_ID)
INNER JOIN Company_Year_Ends AS CYE ON (ADV.Year_End = CYE.Year_End) AND (ADV.AMB_No =
CYE.AMB_No))
INNER JOIN Company_Details CD ON ADV.AMB_No = CD.AMB_No)
INNER JOIN Template_Item_Link TIL ON TIL.Account_Item_ID = ADV.Account_Item_ID)
LEFT JOIN EXCHANGE_RATES AS ER ON (CYE.YEAR_END = ER.DATE_REPORTED) AND
(CYE.REPORTED_CURRENCY = ER.COUNTRY_CODE))
LEFT JOIN USER_EXCHANGE_RATES UER ON UER.Country_Code = ER.Country_Code AND
UER.Date_Reported = ER.Date_Reported AND
UER.User_ID = 'code for test'
WHERE ADV.AMB_No In ('87200', '87201', '85646', '87202', '87204', '86478', '86248',
'86292', '87242', '87209',
'72216', '72178', '85333', '87246', '87247', '87862', '87210', '87211', '87212',
'85105', '85950', '87216', '87217',
'87682', '87220', '87221', '85546', '87222', '86158', '87223', '87679', '85547',
'84036', '87225', '86479', '85814',
'86505', '87250', '85550', '86164', '85203', '86162', '87490', '87530', '86163',
'87249', '87859', '87256', '87226',
'84153', '86285', '87227', '87963', '87863', '84204', '87228', '87229', '87230',
'84105', '87960', '87232', '87964',
'87275', '86954', '87800', '87253', '87254', '84158', '87243', '85491', '85657',
'84037', '86166', '87257', '87258',
'87259', '84114', '87930', '87260', '85572', '85564', '87261', '87262', '85548',
'87449', '87264', '87266', '87267',
'87933', '87269', '85266', '85909', '87357', '86524', '86250', '87290', '87270',
'87271', '85504', '87272', '87408',
'87273', '87274', '87277', '87278', '84120', '87279', '84254', '87280', '85860',
'87876', '87281', '87282', '85249',
'87283', '87284', '87801', '87285', '87693', '84203', '87873', '87292', '87293',
'87295', '87932', '87296', '87298',
'85595', '87860', '86389', '87872', '86373', '87300', '87301', '87378', '87303',
'87304', '87308', '87309', '87310',
'87311', '87312', '87313', '87314', '87966', '87316', '87317', '87319', '87318',
'87320', '87321', '87322', '85397',
'85813', '87325', '85561', '86286', '85083', '87327', '84205', '85511', '87332',
'85508', '87333', '87334', '86181',
'86463', '84119', '87336', '87337', '87338', '87339', '87340', '87341', '85602',
'87343', '86628', '87345', '87346',
'85832', '85812', '87348', '86695', '87349', '86513', '87350', '87351', '87352',
'87233', '84118', '87353', '85935',
'87436', '85650', '87442', '85574', '87354', '87355', '87356', '86653', '86655',
'87603', '84206', '86063', '86852',
'84195', '87931', '86629', '86483', '86596', '86597', '87363', '87364', '87365',
'87358', '87366', '86160', '87371',
'85368', '84143', '87434', '85211', '84207', '85649', '86507', '85207', '85209',
'87376', '87377', '87387', '86142',
'86143', '85551', '84162', '87381', '87382', '87861', '87383', '87385', '85084',
'87302', '87934', '84154', '87935',
'86912', '87976', '87265', '87391', '87392', '86999', '87461', '87397', '87396',
'84299', '87399', '87400', '87401',
'85515', '86129', '87558', '87405', '86485', '86486', '87949', '84163', '87952',
'86487', '87410', '87411', '85353',
'87412', '87413', '87414', '87415', '87416', '87422', '84279', '85941', '87423',
'87425', '84113', '87950', '86522',
'85357', '87429', '87431', '87958', '84297', '87864', '87516', '87642', '87234',
'85643', '85644', '84107', '87437',
'85106', '87438', '85362', '87439', '87440', '85354', '87444', '87445', '87446',
'87447', '87450', '87451', '87918',
'87948', '86301', '87452', '87596', '87454', '86291', '87456', '87457', '87458',
'87460', '87459', '85365', '86488',
'87463', '87465', '87494', '87466', '87467', '87468', '87947', '87471', '86284',
'87476', '85012', '85262', '85315',
'87478', '87479', '87480', '87481', '84156', '85086', '87475', '87482', '84196',
'87483', '87709', '87706', '87486',
'87956', '87954', '87488', '87489','87959', '86252', '87491', '87492', '85250',
'86137', '85265', '87498', '87499',
'84152', '85323', '87501', '87503', '85026', '85514', '87506', '87507', '87597',
'85367', '87510', '87951', '87512',
'87514', '85030', '85188', '85103', '85837', '86247', '87515', '85946', '87517',
'87519', '87520', '87965', '86154',
'87522', '87523', '87802', '87524', '87526', '84194', '87529', '87531', '87533',
'87957', '87538', '86307', '87539',
'87541', '87542', '87543', '85641', '85642', '87803', '87545', '87544', '87546',
'87578', '86126', '85516', '87548',
'87955', '87552', '87551', '87557', '86674', '84106', '84160', '85442', '85660',
'84157', '85662', '86257', '87561',
'87560', '85187', '85549', '85183', '85186', '87565', '87566', '87562', '87567',
'87568', '87569', '87572', '87573',
'87574', '86489', '87575', '87386', '87424', '85448', '87576', '84362', '87577',
'87579', '86884', '87580', '84117',
'85477', '87583', '87584', '87585', '87472', '87588', '84159', '85939', '84161',
'85949', '85107', '87570', '87592',
'85619', '87598', '87599', '85003', '85611', '87600', '87601', '87602', '85363',
'85545', '85844', '87536', '87605',
'87606', '84155', '86297', '86407', '87608', '87967', '85082', '87380', '87610',
'85134', '86222', '85108', '87617',
'87614', '87615', '87611', '87616', '86497', '87619', '87622', '87621', '87624',
'87625', '87627', '85173', '87953',
'87630', '87632', '87633', '85366', '84197', '84116', '87635', '87639', '86512',
'86490', '85543', '87644', '87645',
'87646', '87648', '87115', '85594', '85593', '87653', '87654', '87655', '87804',
'87652', '87649', '87657', '87658',
'87659', '87660', '87661', '87663', '85355', '86133', '85093', '87665', '87666',
'87671', '87672', '85400', '87287',
'87805', '87675', '87676', '87678', '85133', '87677', '87674', '86495', '86169',
'87680', '86273', '86272', '87631',
'86263', 'ZZTEST')
AND Year(ADV.Year_End) = '1999'
AND ADV.Business_Class_ID = '0'
AND TIL.Nat_Template_ID = '622'
ORDER BY CD.Company_Name, ADV.AMB_No, AI.Account_Item, ADV.Account_Item_ID;
Can you tell me a better way for this query?
Thanks a lot
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
