Very Slow Query

[dweb]

Hi

 

I have a 5 database tables that i'm tying together, each table has about 50,000 records.

 

The query i'm using is

 

SELECT u.id, u.name, u.age, p.name, p.code, a.time, a.kin, b.lime, g.happen, (SELECT COUNT(*) FROM `log` WHERE `logid` = u.id) AS tot
FROM users u
LEFT JOIN products p ON p.id = u.prodid
LEFT JOIN ages a ON a.id = u.ageid
LEFT JOIN book b ON b.id = u.bookid
LEFT JOIN group g ON g.id = u.groupid
WHERE u.name != 'admin'
ORDER BY u.id DESC LIMIT 10,40

 

works great but for some reason it's taking around 100 seconds to return data.

 

but if I remove

 

ORDER BY u.id DESC

 

then the query takes around 5 seconds

 

I've read in some groups that to speed up, I should do the ORDER after the query, by doing

 

SELECT * FROM (

SELECT u.id, u.name, u.age, p.name, p.code, a.time, a.kin, b.lime, g.happen, (SELECT COUNT(*) FROM `log` WHERE `logid` = u.id) AS tot
FROM users u
LEFT JOIN products p ON p.id = u.prodid
LEFT JOIN ages a ON a.id = u.ageid
LEFT JOIN book b ON b.id = u.bookid
LEFT JOIN group g ON g.id = u.groupid
WHERE u.name != 'admin'

) ORDER BY u.id DESC LIMIT 10,40

 

But that doesn't seem to work

 

Can anyone help or suggest an alternative

 

Thank you

Edited January 2, 2013 by dweb

[Psycho]

Two things:

 

1. Have you indexed the columns that are being used in your JOINs

 

2. Instead of doing the subquery, just JOIN the log records onto the query and GROUP by them to get the count

 

SELECT u.id, u.name, u.age,
   p.name, p.code,
   a.time, a.kin,
   b.lime,
   g.happen,
   COUNT(logid) AS tot
FROM users u
LEFT JOIN products p ON p.id = u.prodid
LEFT JOIN ages a ON a.id = u.ageid
LEFT JOIN book b ON b.id = u.bookid
LEFT JOIN group g ON g.id = u.groupid
LEFT JOIN log l ON logid = u.id AS tot
WHERE u.name != 'admin'
GROUP BY logid
ORDER BY u.id DESC
LIMIT 10,40

Edited January 2, 2013 by Psycho

[dweb]

Hi Psycho

 

I tried your query idea and it still took 90 seconds

 

I can't index the columns as I dont have direct database access

[Barand]

Do they all need to be left joins? These are much slower than inner joins.

[Psycho]

I can't index the columns as I dont have direct database access

 

You can run queries through PHP, correct?

[dweb]

Do they all need to be left joins? These are much slower than inner joins.

 

thanks, I will try inner joins

[dweb]

You can run queries through PHP, correct?

 

as far as i know, i only have the ability to do select queries, but i could try

 

i also know very little about indexing as not done it before

[cpd]

CREATE INDEX my_column_idx ON table(my_column)

 

That'll index the column you specify and should make your query run faster. Normally you would index columns used in your WHERE clause as these are the conditions it has to search.

[Psycho]

Normally you would index columns used in your WHERE clause as these are the conditions it has to search.

 

You would also index columns used to JOIN your tables. When you JOIN tables the conditions for the JOIN are treated just like a WHERE condition even if not directly in a WHERE clause (i.e. using the ON or USING conditions). In fact, it is probably more important to index columns used for JOINing your tables than those that may only be used in a WHERE clause.

[dweb]

Thanks, i changed the table a bit and indexed the column `name` in the `users` table seems to have made it much faster

 

I have quite a few inserts, updates and deleted from the `users` table, what do I need to do to update the index each time, or is this automatically done for me?

 

Do i have to add another query to each insert, update and delete?

 

thank you

[cpd]

No the DBMS will take care of the indexing period.

[dweb]

cool, thanks