MySQL Query taking forever

Posted on 2011-05-03
Last Modified: 2012-05-11
Hello all.  I have a query that for some reason takes forever to complete with a date range under a week.  Can anyone see any obvious mistakes I am making?  I am running this with PHP and outputting a CSV download.  This database does have quite a few records in routeLogTbl (about 300,000) but the rest are relatively small.  I am not sure why this one query seems to never finish.  even with the start and end date of a week it should produce no more than 1000 records.
$select = "SELECT
  locationTbl.code AS Ship_From,
  locationTbl_1.code AS Ship_To,
  CONCAT(employeeTbl.last_name, ', ', LEFT(employeeTbl.first_name, 1)) AS Driver, AS Pro_Num,
  CONCAT('$', FORMAT(IFNULL(incomeTbl.baseRate, 0), 2)) AS Base_Rate,
  CONCAT('$', FORMAT(IFNULL(incomeTbl.fuel, 0), 2)) AS Fuel_Sur, AS Move_Type,
  LEFT OUTER JOIN employeeTbl
    ON routeLogTbl.employee_id =
    ON = incomeTbl.comments
    ON = routeLogTbl.trip_type
  LEFT OUTER JOIN locationTbl
    ON routeLogTbl.shipper =
  LEFT OUTER JOIN locationTbl locationTbl_1
    ON routeLogTbl.consignee =
 routeLogTbl.date_entered >= '$start'
routeLogTbl.date_entered <= '$end'";

Open in new window

Question by:drhamel69
    LVL 38

    Expert Comment

    by:Aaron Tomosky
    The 5 outer joins don't help. Would your data work with left joins?
    LVL 24

    Expert Comment

    Yes check if you can do with left outer joins for some of them.  Definitely you need to retain it with incomeTbl.  The other thing is check if you have the necessary indexes in place. Start with running an EXPLAIN on the query (see to give you an idea what should be indexed.  Looking at the query my prime candidate would be routeLogTbl.date_entered, then the columns you used in the join clauses.
    LVL 38

    Expert Comment

    by:Aaron Tomosky
    The way it's setup its matching all rows and then limiting the results to the ones you want. That's why it takes so long. If you can do
    From (select * from routeLogTbl where date_entered between '$start' and '$end')

    And left join on that, it would speed things up significantly.
    LVL 24

    Expert Comment

    The way it's setup its matching all rows and then limiting the results to the ones you want. That's why it takes so long.

    I'm not sure if this is true.  Does MySQL always prioritize join clauses over where clauses?  Would be quite dumb of it to do so.  The output of EXPLAIN would show us what is really happening.
    LVL 12

    Expert Comment

    by:Mohamed Abowarda
    In your case, you might consider using another database or updating your version of MySQL.
    LVL 24

    Expert Comment

    @Medo3337 - I don't get where you are coming from.  The author did not give what version is being used so I don't know how you can suggest a version upgrade.  And obviously, changing databases is a major undertaking and so I don't think that should be one of the first options to look at.  You need to understand what's really happening when the query is run, then once you do, there are a lot of things you can do to enhance the performance.  Such as:

    1.  Revise the query
    2.  Add appropriate indexes
    3.  See if other performance enhancing features such as partitioning can be applied.
    LVL 2

    Author Comment

    Here is the explain from PHPinfo
    LVL 24

    Accepted Solution

    There you go.  It doesn't have any index to use for the join with incomeTbl.  Try creating one for incomeTbl.comments
    LVL 107

    Expert Comment

    by:Ray Paseur
    You probably want an index on every column used in WHERE, JOIN, ORDER BY, GROUP BY.  

    This can also be helpful when you have slow queries.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
    Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
    The viewer will learn how to dynamically set the form action using jQuery.
    This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now