CODEWITHSUNDEEP
mysql
Aeykash
Aeykash

Reputation: 135

Anyway to optimize following query?

I have three tables in database Account, Product and Users. Following query that I wrote is taking too long to execute. Is there anyway to optimize this following query?

"SELECT accounts.id AS aucID, accounts.product_id, accounts.end_time, accounts.price, accounts.win_id, 
products.id, products.title, products.ratio, users.id , users.username, users.gender, users.email, users.ip
FROM accounts, products, users
WHERE products.id
IN (
SELECT id
FROM products
WHERE accounts.product_id = id
)
AND users.id
IN (
SELECT id
FROM users
WHERE accounts.win_id = id
)
AND accounts.end_time >= '$monday'

AND accounts.end_time < '$sunday'

GROUP BY accounts.end_time"

Upvotes: 0

Views: 36

Answers (3)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You need to use explicit JOIN instead of implicit.

select 
a.id as aucID,
a.product_id ,
a.end_time,
a.price,
a.win_id,
p.id,
p.title,
p.ratio,
u.id as user_id
u.username,
u.gender,
u.email,
u.ip
from accounts a
join products p on p.id = a.product_id
join users u on u.id = a.win_id
WHERE 
a.end_time >= '$monday'
and a.end_time < '$sunday'
GROUP BY a.end_time

Now you need indexes so first check

show indexes from accounts;
show indexes from accounts;
show indexes from users;

Check if columns are already indexed if not add the following index.

alter table accounts add index product_id_idx(product_id);
alter table accounts add index win_id_idx(win_id);
alter table accounts add index end_time_idx(end_time);  
alter table products add index pid_idx(id);
alter table users add index uid_idx(id);

Note that primary key is by default indexed so you may avoid indexing on 

alter table products add index pid_idx(id);
alter table users add index uid_idx(id);

If they are set to primary key.

In addition make sure that the JOINING keys are always having the same data type

For example 

join products p on p.id = a.product_id

both p.id and a.product_id should be having the same datatype ex : int

similarly

join users u on u.id = a.win_id

After the changes see the query performance.

Upvotes: 1

Sadikhasan
Sadikhasan

Reputation: 18600

Try with INNER JOIN

SELECT accounts.id AS aucID,
       accounts.product_id,
       accounts.end_time,
       accounts.price,
       accounts.win_id,
       products.id,
       products.title,
       products.ratio,
       users.id ,
       users.username,
       users.gender,
       users.email,
       users.ip
FROM accounts
INNER JOIN products ON accounts.product_id = products.id
INNER JOIN users ON accounts.win_id = users.id
WHERE accounts.end_time >= '$monday'
  AND accounts.end_time < '$sunday'
GROUP BY accounts.end_time

Upvotes: 2

hashbrown
hashbrown

Reputation: 3516

why not simplify this -

 SELECT 
    accounts.id AS aucID, 
    accounts.product_id, 
    accounts.end_time, 
    accounts.price, 
    accounts.win_id, 
    products.id, 
    products.title, 
    products.ratio, 
    users.id , 
    users.username, 
    users.gender, 
    users.email, 
    users.ip
 FROM accounts, products, users
 WHERE accounts.product_id = products.id
 AND accounts.win_id = users.id
 AND accounts.end_time >= '$monday'
 AND accounts.end_time < '$sunday'
 GROUP BY accounts.end_time

You may also consider creating indexes in following columns:

accounts.product_id 
accounts.win_id 
accounts.end_time

Upvotes: 1

Related Questions