CODEWITHSUNDEEP
mysqlsqlquery-optimization
user2895890
user2895890

Reputation: 38

Optimizing SQL group by and join query

I have two large tables of users and purchases (respectively 10^6 and 10^8 rows). I'm using mySql to run the query here in the following, but it takes ages to compute! What is the best way to speed up execution? Should I use index or split the query into two queries?

CREATE TABLE user(
uID INTEGER, 
countryCode varchar(2)
);

CREATE TABLE purchases(
uID INTEGER, 
productID INTEGER, 
price INTEGER
);

SELECT U.countryCode AS country, SUM(P.price) AS amount
FROM user U, purchases P
WHERE U.uid = P.uid
GROUP BY U.countryCode
ORDER BY U.countryCode ASC;

I guess the problem is in the Type:All. Explain gives me this:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra  
1   SIMPLE  U   ALL     NULL    NULL    NULL    NULL    9653    Using temporary; Using filesort  
1   SIMPLE  P   ALL     NULL    NULL    NULL    NULL    97816   Using where; Using join buffer

Upvotes: 1

Views: 217

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562300

You need these two indexes:

CREATE INDEX USER_countryCode_uid ON user(countryCode,uid);

CREATE INDEX PURCHASES_uid_price ON purchases(uid,price);

EXPLAIN then improves:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: U
         type: index
possible_keys: USER_countryCode_uid
          key: USER_countryCode_uid
      key_len: 10
          ref: NULL
         rows: 10004
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: P
         type: ref
possible_keys: PURCHASES_uid_price
          key: PURCHASES_uid_price
      key_len: 5
          ref: test.U.uID
         rows: 1
        Extra: Using index

PS: You should define a primary key for every table, though this is not the issue for this particular query.

Upvotes: 2

Suresh
Suresh

Reputation: 1083

Try this

CREATE INDEX countryCode_IDX ON USER(countryCode);

Use this Index in your query.

SELECT U.countryCode AS Country, Sum(P.Price) AS Amount FROM USER U FORCE INDEX (countryCode_IDX) LEFT JOIN Purchases P ON U.uid = P.uid GROUP BY U.countryCode ORDER BY U.countryCode ASC;

Use the foreign key index for uid column.

Upvotes: 1

Related Questions