help with query optimization

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
asgerhallas
Forum Commoner
Posts: 80
Joined: Tue Mar 14, 2006 11:11 am
Location: Århus, Denmark

help with query optimization

Post 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!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

The two tables don't appear to relate to each other.
asgerhallas
Forum Commoner
Posts: 80
Joined: Tue Mar 14, 2006 11:11 am
Location: Århus, Denmark

Post by asgerhallas »

Aren't they connected here?

Code: Select all

spbind.vare=spvarer.id
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

considering your use of parenthesis, that condition will only be run part of the time.
asgerhallas
Forum Commoner
Posts: 80
Joined: Tue Mar 14, 2006 11:11 am
Location: Århus, Denmark

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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`
asgerhallas
Forum Commoner
Posts: 80
Joined: Tue Mar 14, 2006 11:11 am
Location: Århus, Denmark

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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`
asgerhallas
Forum Commoner
Posts: 80
Joined: Tue Mar 14, 2006 11:11 am
Location: Århus, Denmark

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

...
Post Reply