CODEWITHSUNDEEP
mysqlsqlquery-optimization
Matt Dunbar
Matt Dunbar

Reputation: 950

big table query optimization (mysql)

This table contains 1.2mill results, and I can't edit it as there are applications which I don't have the source code to accessing it as well. I'd like to add a quantity field, but I can't.

Here is a query I am using:

  SELECT SUM(assets.hourlyEarnings) as earnings, 
         assets_inventory.uid
    FROM (assets)
    JOIN assets_inventory ON assets.id = assets_inventory.assetID
   WHERE assets_inventory.uid IN (SELECT users.uid 
                                    FROM users 
                                   WHERE users.assetTime < 1305350756)
GROUP BY uid

There are many duplicate records.

Here is the table:

CREATE TABLE IF NOT EXISTS assets_inventory (
  id int(11) NOT NULL AUTO_INCREMENT,
  uid bigint(20) NOT NULL,
  assetID int(11) NOT NULL,
  PRIMARY KEY (id),
  KEY uid (uid)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1231992 ;

On average I'm taking 6-7 seconds to pull the results, any suggestion to speed this up would be appreciated!

Upvotes: 1

Views: 399

Answers (1)

OMG Ponies
OMG Ponies

Reputation: 332571

If you want a list of all uid values, whether or not there are earnings associated:

   SELECT DISTINCT
          ai.uid,
          COALESCE(x.earnings, 0) AS earnings
     FROM ASSETS_INVENTORY ai
LEFT JOIN (SELECT t.id,
                  SUM(t.hourlyearnings) AS earnings
             FROM ASSETS t
         GROUP BY t.id) x ON x.id = ai.assetid
    WHERE EXISTS (SELECT NULL
                    FROM USERS u
                   WHERE u.uid = ai.uid
                     AND u.assettime < 1305350756)

Otherwise:

  SELECT ai.uid,
         SUM(a.hourlyearnings) AS earnings
    FROM ASSETS_INVENTORY ai
    JOIN ASSETS a ON a.id = ai.assetid
   WHERE EXISTS (SELECT NULL
                   FROM USERS u
                  WHERE u.uid = ai.uid
                    AND u.assettime < 1305350756)
GROUP BY ai.uid

...or:

  SELECT ai.uid,
         SUM(a.hourlyearnings) AS earnings
    FROM ASSETS_INVENTORY ai
    JOIN ASSETS a ON a.id = ai.assetid
    JOIN USERS u ON u.uid = ai.uid
                AND u.assettime < 1305350756
GROUP BY ai.uid

Upvotes: 1

Related Questions