Anyway to optimize following query?

Question

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"

You use PHP to create an SQL statement via string interpolation. This is all kinds of wrong. The first way to improve this is by converting it to a parameterized query. If you want to make a meaningful change, read about SQL injection and about mysqli and query parameters. — Tomalak, Jun 25 '14 at 08:50

score 2 · Accepted Answer · answered Jun 25 '14 at 08:51

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

score 1 · Answer 2 · answered Jun 25 '14 at 08:50

1

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

answered Jun 25 '14 at 08:50

hashbrown

3,438
1
19
37

Old style join syntax is from the last *millennium*. – Tomalak Jun 25 '14 at 08:51
@Tomalak so? Does it have any impact on performance? Refer - http://stackoverflow.com/questions/1613304/ansi-joins-versus-where-clause-joins – hashbrown Jun 25 '14 at 08:54
I'm saying that this has been deprecated since forever, not that it's slower. – Tomalak Jun 25 '14 at 08:58

score 1 · Answer 3 · answered Jun 25 '14 at 09:00

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.

Anyway to optimize following query?

3 Answers3