Re: Server just hanging (Copying to tmp table)

[Thomas Deliduka]

Your Query change:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: xno_items
type: ref
possible_keys: active
key: active
key_len: 4
ref: const
rows: 1647
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: xno_prod_index
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 64
ref: finerkitchens_com.xno_items.itemid,const
rows: 1
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: xno_item_cat
type: index
possible_keys: NULL
key: PRIMARY
key_len: 8
ref: NULL
rows: 5155
Extra: Using index
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: xno_categories
type: eq_ref
possible_keys: PRIMARY,active
key: PRIMARY
key_len: 4
ref: finerkitchens_com.xno_item_cat.catid
rows: 1
Extra:
*************************** 5. row ***************************
id: 1
select_type: SIMPLE
table: xno_manufacturers
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: finerkitchens_com.xno_items.mfgid
rows: 1
Extra:

Original Query:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: xno_items
type: ref
possible_keys: PRIMARY,active
key: active
key_len: 4
ref: const
rows: 1647
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: xno_prod_index
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 64
ref: finerkitchens_com.xno_items.itemid,const
rows: 1
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: xno_item_cat
type: index
possible_keys: NULL
key: PRIMARY
key_len: 8
ref: NULL
rows: 5155
Extra: Using index
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: xno_categories
type: eq_ref
possible_keys: PRIMARY,active
key: PRIMARY
key_len: 4
ref: finerkitchens_com.xno_item_cat.catid
rows: 1
Extra: Using where
*************************** 5. row ***************************
id: 1
select_type: SIMPLE
table: xno_manufacturers
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: finerkitchens_com.xno_items.mfgid
rows: 1
Extra:


Incidentally, I tried putting the xno_prod_index.word ='thermometer' in the "where" clause and left the xno_categories.active = 1 up in the join and then disabled a category and ran the query. The Items in the disabled category still showed up. When I used my original query, they did not. It is as if the MySQL SErver is ignoring the additional filters on the join statement.

I know enough to be dangerous. Thanks for being patient!