Query causes (Errcode: 28)

[me]

I have a shopping cart that errors out when try to do a search

select p.products_ship _price, p.products_id, p.products_mode l,
pd.products_nam e, p.products_quan tity, p.products_imag e, p.products_pric e,
p.products_date _added, p.products_last _modified, p.products_date _available,
p.products_stat us, p2c.categories_ id from products p, products_descri ption
pd, products_to_cat egories p2c where p.products_id = pd.products_id and
pd.language_id = '1' and p.products_id = p2c.products_id and
pd.products_nam e like '%3219%' or p.products_mode l like '%3219%' order by
pd.products_nam e

If I remove either

or p.products_mode l like '%3219%'

OR

pd.products_nam e like '%3219%'

The query will run fine 30 Results for Model and 3 products for Name. While
it is running it will load the Temp directory. I moved the DB to one of my
64bit boxes with 8GB Ram and a 200GB drive mysql 4.1.12 Max and it just
runs longer before it throws the error. The Database has 11000 products in
it but we have the same issue with a database with 5K products.

Can this query be the problem. It is from OSC Max
--
Eric Haskins
Lead Software Developer
Merchant 2020
[phone] ext. 206
[phone] Cell
er**@merchant20 20.com

[Gordon Burditt]

>I have a shopping cart that errors out when try to do a search

select p.products_ship _price, p.products_id, p.products_mode l,
pd.products_na me, p.products_quan tity, p.products_imag e, p.products_pric e,
p.products_dat e_added, p.products_last _modified, p.products_date _available,
p.products_sta tus, p2c.categories_ id from products p, products_descri ption
pd, products_to_cat egories p2c where p.products_id = pd.products_id and
pd.language_ id = '1' and p.products_id = p2c.products_id and
pd.products_na me like '%3219%' or p.products_mode l like '%3219%' order by
pd.products_na me

If I remove either

or p.products_mode l like '%3219%'

OR

pd.products_na me like '%3219%'

The query will run fine 30 Results for Model and 3 products for Name. While
it is running it will load the Temp directory. I moved the DB to one of my
64bit boxes with 8GB Ram and a 200GB drive mysql 4.1.12 Max and it just
runs longer before it throws the error. The Database has 11000 products in
it but we have the same issue with a database with 5K products.

error 28 on my box (FreeBSD) is "no space left on device".
Which partition is running out of disk space?

Gordon L. Burditt

[me]

> error 28 on my box (FreeBSD) is "no space left on device".

Which partition is running out of disk space?

Gordon L. Burditt

/ partition

Thats why it takes longer for error to develop on the 64 bit box 150Gb
free. The other bo has only 50GB free so it errors out sooner. You can
watch a file in the /tmp directory grow while the search is running

Eric

"Gordon Burditt" <go***********@ burditt.org> wrote in message
news:11******** *****@corp.supe rnews.com...

I have a shopping cart that errors out when try to do a search

select p.products_ship _price, p.products_id, p.products_mode l,
pd.products_n ame, p.products_quan tity, p.products_imag e, p.products_pric e,
p.products_da te_added, p.products_last _modified,
p.products_da te_available,
p.products_st atus, p2c.categories_ id from products p, products_descri ption
pd, products_to_cat egories p2c where p.products_id = pd.products_id and
pd.language_i d = '1' and p.products_id = p2c.products_id and
pd.products_n ame like '%3219%' or p.products_mode l like '%3219%' order by
pd.products_n ame

If I remove either

or p.products_mode l like '%3219%'

OR

pd.products_n ame like '%3219%'

The query will run fine 30 Results for Model and 3 products for Name.
While
it is running it will load the Temp directory. I moved the DB to one of my
64bit boxes with 8GB Ram and a 200GB drive mysql 4.1.12 Max and it just
runs longer before it throws the error. The Database has 11000 products
in
it but we have the same issue with a database with 5K products.

[Felix Geerinckx]

On 04/08/2005, me wrote:

I have a shopping cart that errors out when try to do a search
...
and pd.products_nam e like '%3219%' or p.products_mode l like '%3219%'
order by pd.products_nam e

Are you sure you don't want

and (pd.products_na me like '%3219%' or p.products_mode l like '%3219%')

here?

--
felix

[andreas.maurer1971]

Felix Geerinckx schrieb:

On 04/08/2005, me wrote:

I have a shopping cart that errors out when try to do a search
...
and pd.products_nam e like '%3219%' or p.products_mode l like '%3219%'
order by pd.products_nam e

Are you sure you don't want

and (pd.products_na me like '%3219%' or p.products_mode l like '%3219%')

here?

--
felix

Hi,

I don't know if I'm right but could it be that you produce a Cartesian
Product with your query?
Maybe it might help to use a real (INNER) JOIN like

SELECT p.products_ship _price, p.products_id, ..., p2c.categories_ id
FROM products p INNER JOIN products_descri ption pd ON p.products_id =
pd.products_id
INNER JOIN products_to_cat egories p2c ON p.products_id =
p2c.products_id
WHERE pd.language_id = '1' AND pd.products_nam e like '%3219%' OR
p.products_mode l like '%3219%'
ORDER BY pd.products_nam e

Please note that I am NOT SURE which conditions are the most important
ones and whether or not you will have to use brackets ("(..)") in order
to group the WHERE... AND.. OR.. parts in order to get the desired
result.
As well I'm NOT SURE if I got all JOINS correct! Please double check
before you use it!

Anyway, I'm pretty sure that you have to use the JOIN keyword in order
to make your query faster.

HTH,

Andy

[me]

As well I'm NOT SURE if I got all JOINS correct! Please double check
before you use it!

Anyway, I'm pretty sure that you have to use the JOIN keyword in order
to make your query faster.

HTH,

Andy

Andy Thx a few tweaks and it did the trick!!!!

Eric