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).
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?
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`
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
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.
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.
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`