Optimized or not?

[ergecs]

Hi, I have a problem about this sql and need your opinions.

 

Two tables. item and item_aux_property

 

Table item has "id", "is_online", "category_1_id"

Table item_aux_property has "item_id" and "value"

 

(Tables have other columns but not important for now)

 

Let say, I want to get items from first table with values "AMD", "Hp Compaq", "Dual Core", "4 GB", "Office Use" from the second table

 

I use a query like this. To run this query it takes 0.0071 sec on my local server. (Pentium 4)

 

But when I run this query on my VPS (which is much better than P4) it stucks mysql server, cpu load becomes 190% until I restart mysql.

 

Is this query optimum query or is there a better way to do this?

 

SELECT t1.*,t1.id as item_id ,t21.*
FROM item t1 JOIN item_aux_property t21 ON t1.id = t21.item_id AND 
t21.value = 'AMD' JOIN item_aux_property t22 ON t1.id = t22.item_id AND 
t22.value = 'Hp Compaq' JOIN item_aux_property t23 ON t1.id = t23.item_id AND 
t23.value = 'Dual Core' JOIN item_aux_property t24 ON t1.id = t24.item_id AND 
t24.value = '4 GB' JOIN item_aux_property t25 ON t1.id = t25.item_id AND 
t25.value = 'Office Use'
WHERE is_online = 'true' AND category_1_id = 50

[fenway]

Why do you have constants in your JOIN on clause? Move this to the where clause.  Let's see the EXPLAIN before and after.

[ergecs]

Ok I tested this sql on 5 different machines

 

#1

VPS (4 CPUs, A couple of sites on VPS but the biggest one is this account)

Apache Version : 1.3.37

PHP Version : 4.4.7

MySQL Version : 4.2.11-standard

 

Result : MySQL Stucks, CPU Load increases rapidly up tp 190%

 

#2

Regular hosting account (4 CPUs, Probably many sites on the server)

Apache Version : 1.3.41

PHP Version : 4.4.8

MySQL Version : 4.2.11-standard

 

Result : Sometimes executes the sql but usually it does not. MySQL doesn't stuck, no idea about CPU Load (regular hosting account)

 

#3

My local server (Dual CPU, etc)

Apache Version : 2.2.3

PHP Version : 5

MySQL Version : 5.0.22

 

Result : It takes only 0.0246 sec to execute sql. Everything is fine

 

#4

My local test server (Celeron )

Apache Version : 2.2.3

PHP Version : 5

MySQL Version : 5.0.22

 

Result : It takes only 0.0967 sec to execute sql. Everything is fine

 

#5

VPS (8 CPUs, Only one account on VPS)

Apache Version : 1.3.39

PHP Version : 5.2.5

MySQL Version : 5.0.45-community

 

Result : It takes only 0.0082 sec to execute sql. Everything is fine

 

Why do you have constants in your JOIN on clause? Move this to the where clause.  Let's see the EXPLAIN before and after.

 

I didn't understand what you meant by "constants", can you explain?

[ergecs]

Explain is

 

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE t21 ALL NULL NULL NULL NULL 2825 Using where

1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 cyprus_pc_v7.t21.item_id 2 Using where

1 SIMPLE t22 ALL NULL NULL NULL NULL 2825 Using where

1 SIMPLE t23 ALL NULL NULL NULL NULL 2825 Using where

1 SIMPLE t24 ALL NULL NULL NULL NULL 2825 Using where

1 SIMPLE t25 ALL NULL NULL NULL NULL 2825 Using where

[aschk]

Wow, serious lack of indexes being used...

Also your SQL query seems to be JOIN'ing the same table several times, but for what reason? The only reasoning I could see would be to get a single row result, instead of multiple rows (which is should be perfectly fine).

 

SELECT * 
FROM item i
JOIN item_aux_property iap ON iap.item_id = i.id
WHERE iap.value IN ('AMD','Hp Compaq','Dual Core','4 GB','Office Use')
AND is_online = 'true' AND category_1_id = 50

[aschk]

Actually, having worked by original answered and reviewing it, I think I see what is being done. Each item HAS a set of properties (auxiliary), but the OP is looking to SELECT the item that has several particular properties, hence the recursive INNER JOINs to eliminate results that DON'T have the required auxiliary properties.

[ergecs]

My query should return only 1 result, but your query returns 126 results which is not correct. All selected items must have all of these properties 'AMD','Hp Compaq','Dual Core','4 GB','Office Use' and there is only one item that matches. My sql works but not with MySQL 4.1.22, only 5 and above. Is there a way to do it with mysql 4?

[fenway]

You still need indexes.