any advice to optomize a search query? runs, but slowly...

[slushpuppie]

(NOTE: i'm changing the names of the tables to make this example a little clearer and hide my project, so if i mis-spelled "items" as "item" in a table name or something like that in my example, that's not an issue)

 

 SELECT DISTINCT `items`.name, `items`.id
FROM `items`
WHERE 1
AND id
IN (
SELECT iid
FROM `item_markings`
WHERE `item_markings`.mid = '9'
AND `item_markings`.amount >=10
)
AND id
IN (
SELECT iid
FROM `item_markings`
WHERE `item_markings`.mid = '73'
AND `item_markings`.amount <=100
)
ORDER BY `items`.name
LIMIT 0 , 30 

 

relevant information about the tables.

table `items` has a unique id field called 'id'.

table `item_markings` has a field called 'iid', which "points" to `items`.id, and 'mid', which "points" to a marking in another table, which right now is irrelevant because i'm passing this 'mid' value into my script.

 

i need to be able to see if an item has a certain marking(s), up to 3 of them.

 

for example:

`items`

"id" | "name"

1 | dog

2 | cat

 

`item_markings`

"iid" | "mid"

1 | 9

1 | 73

2 | 9

3 | 55

 

and say i'm looking for items which have mids of 9 AND 73... i cannot do something like "WHERE mid = 9 OR mid = 73", because that would return 'iid' 2, because it has 9. but i also cannot do "WHERE mid = 9 AND mid = 73" because mid does not equal 9 and 73 at the same time.

 

my initial query at the top of this thread works, however these tables are pretty huge, (2.2 million rows in 'items' and 8.3 million rows in 'item_markings').

 

does anyone know of a different method i could try to try and speed this process up? when i ran it just now i got: "Showing rows 0 - 29 (157 total, Query took 17.8154 sec)" - which really isn't acceptable since i may have hundreds of people running this query at a time.

 

many thanks.

[slushpuppie]

update: i think i'm going to go with smaller querries, and then use PHP to find the overlapping (ie matches) in arrays... i did some samples and it seems like it'll work faster that way.

[Barand]

Wondering if this approach helps

SELECT DISTINCT i.name, i.id
FROM items i
INNER JOIN item_markings m1 ON i.id = m1.iid AND m1.mid = 9
INNER JOIN item_markings m2 ON i.id = m2.iid AND m2.mid = 73

[slushpuppie]

that does seem to work! never occured to me to put an AND clause in the ON condition... nice.

 

thanks!

[slushpuppie]

Showing rows 0 - 29 (161 total, Query took 12.8863 sec)

 

query still took quite a while(but your method is definitely faster), i'll have to do some testing to see if your method or my php method will be easier on the server.

[Barand]

You could also try same structure but conventional WHERE clause and see if it is any faster

 

SELECT DISTINCT i.name, i.id

FROM items i

INNER JOIN item_markings m1 ON i.id = m1.iid

INNER JOIN item_markings m2 ON i.id = m2.iid

WHERE m1.mid = 9 AND m2.mid = 73

[slushpuppie]

the first method you suggested is faster, with the AND in the ON

[Barand]

I'm assuming that id is primary key, but do you have indexes on "mid" and "iid" in the item_markings table?