MySQL Forums
Forum List  »  Optimizer & Parser

Query needs to be optimized
Posted by: ponvictor abraham
Date: June 29, 2010 03:39AM


I need to optimize the following query.

The Query i had written touches 2 different databases & 8 different tables.
Mostly i need to do a LEFT JOIN operations with other tables.

The main table which is used to query is posting which joins with "job, board, hits & clicks" table from one database and "board_instance, usr_boards" from other database.

The posting table has more than "5500000" records and hits & views contains more than "10000000" records.

The query needs to fetch different boards pipeline_id from table board in database A, board instance name from board_instance, media cost table in database B, total number of job reqs per pipeline_id, total clicks & views per board.

The query i am using follows. Its taking too much time to retrieve the data. Sometimes the mysql even hungs

Can anyone help me to sort out this issue with an optimized query for the above requirement

b.board_name AS 'pipeline_id', AS 'board_instance_name',
bi.media_cost AS 'fee_media',
COUNT(j.requisition_number) as 'Postings',
COUNT(DISTINCT(j.requisition_number)) as 'Reqs',
COUNT(v.posting_id) as 'Views',
COUNT(c.posting_id) as 'Clicks'
FROM annwn_sivakumar.board b JOIN
annwn_sivakumar.posting p
JOIN annwn_sivakumar.job j ON
LEFT JOIN usr_boards ub ON ub.usr_id=j.company_id
LEFT JOIN ui_sivakumar.board_instance bi
ON bi.pipeline_id=b.board_name AND
LEFT JOIN annwn.ars_views v ON
LEFT JOIN annwn.ars_hits c ON
WHERE j.company_id=133
AND p.timestamp >='2010-02-02'
AND p.timestamp<='2010-06-02'
GROUP BY b.board_name;


Options: ReplyQuote

Written By
Query needs to be optimized
June 29, 2010 03:39AM
June 30, 2010 08:50AM
July 01, 2010 09:43PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.