MySQL Forums
Forum List  »  Optimizer & Parser

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

Hi,

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

SELECT
b.board_name AS 'pipeline_id',
bi.name 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
ON b.id=p.board_id
JOIN annwn_sivakumar.job j ON j.id=p.job_id
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 bi.id=ub.board_instance_id
LEFT JOIN annwn.ars_views v ON p.id=v.posting_id
LEFT JOIN annwn.ars_hits c ON p.id=c.posting_id
WHERE j.company_id=133
AND p.timestamp >='2010-02-02'
AND p.timestamp<='2010-06-02'
GROUP BY b.board_name;


Thanks
Victor

Options: ReplyQuote


Subject
Views
Written By
Posted
Query needs to be optimized
3122
June 29, 2010 03:39AM
1477
June 30, 2010 08:50AM
1403
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.