Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

Ovid (2709)

Ovid
  (email not shown publicly)
http://publius-ovidius.livejournal.com/
AOL IM: ovidperl (Add Buddy, Send Message)

Stuff with the Perl Foundation. A couple of patches in the Perl core. A few CPAN modules. That about sums it up.

Journal of Ovid (2709)

Wednesday October 03, 2007
05:48 AM

Learning MySQL Optimization

[ #34596 ]

Since I recently posted a plea for MySQL query optimization help, I've been reading like mad about how to optimize MySQL queries. I'm not perfect, but I'm getting the hang of it. I have one query which used to take about 15 minutes to run now taking about five seconds. It didn't help that the query has hideously complicated logic with a bizarre edge case (I've changed all of the table and column names):

SELECT DATE_FORMAT( m1.processed, '%Y-%m') as `When`,
       m1.aid  AS 'A ID',
       c.fname AS 'First Name',
       c.sname AS 'Last Name',
       SUM(
         ( m1.from_foo + m1.from_bar )
         -
         ( m1.to_foo + m1.to_bar )
       ) AS Total,
       IF (a.extra IS NULL, 'Yes', 'No') AS Disabled
FROM   manifests m1
LEFT JOIN  regulators            ON m1.manifest = r_manifest
LEFT JOIN  manifests m2          ON m2.manifest = o_manifest
LEFT JOIN  alligator_crutches ac ON b.dedid     = ac.dedid
INNER JOIN alligators a          ON a.aid       = m1.aid
INNER JOIN customers c           ON c.aid       = m1.aid
INNER JOIN baubles b             ON m1.manifest = b.manifest
WHERE  m1.aid    != 0
  AND  m1.aid    IS NOT NULL
  AND  m1.processed BETWEEN '2007-08-01' AND '2007-08-31'
  AND  (
    m2.processed IS NULL
      OR
    (
        m1.manifest = r_manifest
          AND
        m2.processed BETWEEN '2007-08-01' AND '2007-08-31'
    )
  )
GROUP BY m1.aid
ORDER BY Total DESC

All things considered, I'm feeling quite accomplished. It's not perfect, but it's a darn good start.

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.