MySql Query takes forever...

Question

hi I am doing A query to get some product info, but there is something strange going on, the first query returns resultset fast (.1272s) but the second (note that I just added 1 column) takes forever to complete (28-35s), anyone know what is happening?

query 1

SELECT
p.partnumberp,
p.model,
p.descriptionsmall,
p.brandname,
sum(remainderint) stockint
from
inventario_dbo.inventoryindetails ind
left join purchaseorders.product p on (p.partnumberp = ind.partnumberp)
left join inventario_dbo.inventoryin ins on (ins.inventoryinid= ind.inventoryinid)
group by partnumberp, projectid

query 2

SELECT
p.partnumberp,
p.model,
p.descriptionsmall,
p.brandname,
p.descriptiondetail,
sum(remainderint) stockint
from
inventario_dbo.inventoryindetails inda
left join purchaseorders.product p on (p.partnumberp = inda.partnumberp)
left join inventario_dbo.inventoryin ins on (ins.inventoryinid= inda.inventoryinid)
group by partnumberp, projectid

Answer 1

Is descriptiondetail a really large column? Sounds like it could be a lot of text compared to the other fields based on its name, so maybe it just takes a lot more time to read from disk, but if you could post the schema detail for the purchaseorders.product table or maybe the average length of that column that would help.

Otherswise I would try running the query a few times and see you consistently get the same time results. Could just be load on the database server the time you got the slower result.

Answer 2

You shouldn't group by some columns and then select other columns unless you use aggregate functions. Only p.partnumberp and sum(remainderint) make sense here. You're doing a huge join and select and then the results for most rows just end up getting discarded.

You can make the query much faster by doing an inner select first and then joining that to the remaining tables to get your final result for the last few columns.

The inner select should look something like this:

select p.partnumberp, projectid, sum(remainderint) stockint
    from inventario_dbo.inventoryindetails ind
    left join purchaseorders.product p on (p.partnumberp = ind.partnumberp)
    left join inventario_dbo.inventoryin ins on (ins.inventoryinid = ind.inventoryinid)
    group by partnumberp, projectid

After the join:

select T1.partnumberp, T1.projectid, p2.model, p2.descriptionsmall, p2.brandname, T1.stockint
from
    (select p.partnumberp, projectid, sum(remainderint) stockint
        from inventario_dbo.inventoryindetails ind
        left join purchaseorders.product p on (p.partnumberp = ind.partnumberp)
        left join inventario_dbo.inventoryin ins on (ins.inventoryinid = ind.inventoryinid)
        group by partnumberp, projectid) T1
    left join purchaseorders.product p2 on (p2.partnumberp = T1.partnumberp)