Page 1 of 1
help with query optimization
Posted: Thu Apr 13, 2006 6:42 am
by asgerhallas
Hi,
A have these to queries:
Code: Select all
SELECT spvarer.* FROM spbind, spvarer WHERE spvarer.pris!=0 AND spbind.model=31477 AND spbind.vare=spvarer.id ORDER BY spvarer.webtext
AND
Code: Select all
SELECT spvarer.* FROM spvarer WHERE spvarer.allmodels=1 ORDER BY spvarer.webtext
They execute fast enough in MySQL, but I would like to combinde them into somehting like this:
Code: Select all
SELECT spvarer.* FROM spbind, spvarer WHERE spvarer.pris!=0 AND (spvarer.allmodels=1 OR (spbind.model=31477 AND spbind.vare=spvarer.id)) ORDER BY spvarer.webtext
But when I do that it doesn't execute at all! Or it tries for several minutes and results in a "#1030 - Got error 28 from storage engine". Is there a way to optimize the above query?
(spvarer has about 30000 posts, spbind has about 500000).
Thank you very much!
Posted: Thu Apr 13, 2006 8:33 am
by feyd
The two tables don't appear to relate to each other.
Posted: Thu Apr 13, 2006 8:42 am
by asgerhallas
Aren't they connected here?
Posted: Thu Apr 13, 2006 8:48 am
by feyd
considering your use of parenthesis, that condition will only be run part of the time.
Posted: Thu Apr 13, 2006 8:59 am
by asgerhallas
yes, that was I wanted it to do... somthing like this:
Return all items from the table "spvarer" where "allmodels=1"
AND all the items in the same table that is connected to the given model (in this case 31477, all connections are listed in "spbind")
Should I do something with the table "spbind" on the left side of the OR even though I don't need the connection to spbind in case allmodels=1?
Posted: Thu Apr 13, 2006 9:07 am
by feyd
Try something more like this.
Code: Select all
SELECT
`spvarer`.*
FROM
`spbind`
INNER JOIN
`spvarer`
ON
`spbind`.`vare` = `spvarer`.`id`
WHERE
`spvarer`.`pris` != 0
AND
(
`spvarer`.`allmodels` = 1
OR
`spbind`.`model` = 31477
)
ORDER BY
`spvarer`.`webtext`
Posted: Thu Apr 13, 2006 9:29 am
by asgerhallas
Thanks! That leads me in the right direction.
The only problem with your suggestion is that it doesn't return any rows from spvarer if there is no "spvarer.id=spbind.vare" relation - and there will never be such a relation if "allmodels=1". I ended up with this
Code: Select all
SELECT DISTINCT spvarer . *
FROM spbind, spvarer
WHERE spvarer.pris !=0
AND spbind.model =31477
AND (
spbind.vare = spvarer.id
OR spvarer.allmodels =1
)
ORDER BY spvarer.webtext
Where the relation between spvarer and spbind is "optional". It works but it is still very slow though. Any pointers at where to start looking for optimizing a query like this then? I guess the JOIN keyword is the way to do it, but I really can't figure out how to make it join only when allmodels!=1.
Posted: Thu Apr 13, 2006 9:39 am
by feyd
Your code is relying on an INNER JOIN (data must match in tables involved to continue creating a result). What you just described is a LEFT JOIN scenario.
Code: Select all
SELECT
*
FROM
`spbind`
LEFT JOIN
`spvarer`
ON
`spvarer`.`allmodels` != 1
AND
`spbind`.`vare` = `spvarer`.`id`
WHERE
`spvarer`.`pris` != 0
AND
`spbind`.`model` = 31477
ORDER BY
`spvarer`.`webtext`
Posted: Thu Apr 13, 2006 10:24 am
by asgerhallas
Oh... that's perfect, now I get it. Seems like I have to do some reading on SQL joins
Thanks a lot!!!
BTW how do you do those SQL-code blocks?
Posted: Thu Apr 13, 2006 10:36 am
by feyd