Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 403
  • Last Modified:

MySQL Query taking forever

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,
  routeLogTbl.pro AS Pro_Num,
  routeLogTbl.date_entered,
  CONCAT('$', FORMAT(IFNULL(incomeTbl.baseRate, 0), 2)) AS Base_Rate,
  CONCAT('$', FORMAT(IFNULL(incomeTbl.fuel, 0), 2)) AS Fuel_Sur,
  tripTypeTbl.name AS Move_Type,
  routeLogTbl.schedule_time,
  routeLogTbl.depart_time,
  routeLogTbl.arrival_time,
  routeLogTbl.pu_arrival_time
FROM
  routeLogTbl
  LEFT OUTER JOIN employeeTbl
    ON routeLogTbl.employee_id = employeeTbl.id
  LEFT OUTER JOIN incomeTbl
    ON routeLogTbl.pro = incomeTbl.comments
  LEFT OUTER JOIN tripTypeTbl
    ON tripTypeTbl.id = routeLogTbl.trip_type
  LEFT OUTER JOIN locationTbl
    ON routeLogTbl.shipper = locationTbl.id
  LEFT OUTER JOIN locationTbl locationTbl_1
    ON routeLogTbl.consignee = locationTbl_1.id
WHERE
 routeLogTbl.date_entered >= '$start'
and
routeLogTbl.date_entered <= '$end'";

Open in new window

0
drhamel69
Asked:
drhamel69
1 Solution
 
Aaron TomoskyTechnology ConsultantCommented:
The 5 outer joins don't help. Would your data work with left joins?
0
 
johanntagleCommented:
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 http://dev.mysql.com/doc/refman/5.0/en/explain.html) 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.
0
 
Aaron TomoskyTechnology ConsultantCommented:
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
johanntagleCommented:
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.
0
 
Mohamed AbowardaSoftware EngineerCommented:
In your case, you might consider using another database or updating your version of MySQL.
0
 
johanntagleCommented:
@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.
0
 
drhamel69Author Commented:
Here is the explain from PHPinfo
EXPLAIN-SQL.bmp
0
 
johanntagleCommented:
There you go.  It doesn't have any index to use for the join with incomeTbl.  Try creating one for incomeTbl.comments
0
 
Ray PaseurCommented:
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.
http://dev.mysql.com/doc/refman/5.1/en/using-explain.html
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now