SEO

#1 (**permalink**) 02-28-2008, 10:30 AM

Hi there,

Although all tables involved in this query seem to use indexes for the
id fields, they somtimes take up to 6 seconds:

# Time: 071204 0:05:37
# Query_time: 5 Lock_time: 0 Rows_sent: 1 Rows_examined: 57035
SELECT
p.products_id as v_products_id,
p.products_model as v_products_model,
p.products_image as v_products_image,
p.products_price as v_products_price,
p.products_weight as v_products_weight,
p.products_date_added as v_date_avail,
p.products_tax_class_id as v_tax_class_id,
p.products_quantity as v_products_quantity,
p.manufacturers_id as v_manufacturers_id,
subc.categories_id as v_categories_id
FROM
products as p,
categories as subc,
products_to_categories as ptoc
WHERE
p.products_id = ptoc.products_id AND
p.products_model = '0775P21' AND
ptoc.categories_id = subc.categories_id;

Any pointers as to what else I might be missing would be very much
appreciated.
Nick

#2 (**permalink**) 02-28-2008, 10:30 AM

On Tue, 04 Dec 2007 00:22:13 +0100, Nick Weisser <nick.weisser@gmail.com>
wrote:

> Hi there,
>
> Although all tables involved in this query seem to use indexes for the
> id fields, they somtimes take up to 6 seconds:
>
> # Time: 071204 0:05:37
> # Query_time: 5 Lock_time: 0 Rows_sent: 1 Rows_examined: 57035
> SELECT
> p.products_id as v_products_id,
> p.products_model as v_products_model,
> p.products_image as v_products_image,
> p.products_price as v_products_price,
> p.products_weight as v_products_weight,
> p.products_date_added as v_date_avail,
> p.products_tax_class_id as v_tax_class_id,
> p.products_quantity as v_products_quantity,
> p.manufacturers_id as v_manufacturers_id,
> subc.categories_id as v_categories_id
> FROM
> products as p,
> categories as subc,
> products_to_categories as ptoc
> WHERE
> p.products_id = ptoc.products_id AND
> p.products_model = '0775P21' AND
> ptoc.categories_id = subc.categories_id;
>
> Any pointers as to what else I might be missing would be very much
> appreciated.

I rather like more explicet joins, however this should work. Why do you
coin categories though, if you only want the id from ptoc?

I'd use this:
SELECT
p.products_id as v_products_id,
p.products_model as v_products_model,
p.products_image as v_products_image,
p.products_price as v_products_price,
p.products_weight as v_products_weight,
p.products_date_added as v_date_avail,
p.products_tax_class_id as v_tax_class_id,
p.products_quantity as v_products_quantity,
p.manufacturers_id as v_manufacturers_id,
ptoc.categories_id as v_categories_id
FROM products as p
LEFT JOIN products_to_categories as ptoc
ON p.products_id = ptoc.products_id
WHERE p.products_model = '0775P21'
--
Rik Wasmus

#3 (**permalink**) 02-28-2008, 10:30 AM

On 4 Dec, 02:26, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
> On Tue, 04 Dec 2007 00:22:13 +0100, Nick Weisser <nick.weis...@gmail.com>
> wrote:
>
>
>
> > Hi there,
>
> > Although all tables involved in this query seem to use indexes for the
> > id fields, they somtimes take up to 6 seconds:
>
> > # Time: 071204 0:05:37
> > # Query_time: 5 Lock_time: 0 Rows_sent: 1 Rows_examined: 57035
> > SELECT
> > p.products_id as v_products_id,
> > p.products_model as v_products_model,
> > p.products_image as v_products_image,
> > p.products_price as v_products_price,
> > p.products_weight as v_products_weight,
> > p.products_date_added as v_date_avail,
> > p.products_tax_class_id as v_tax_class_id,
> > p.products_quantity as v_products_quantity,
> > p.manufacturers_id as v_manufacturers_id,
> > subc.categories_id as v_categories_id
> > FROM
> > products as p,
> > categories as subc,
> > products_to_categories as ptoc
> > WHERE
> > p.products_id = ptoc.products_id AND
> > p.products_model = '0775P21' AND
> > ptoc.categories_id = subc.categories_id;
>
> > Any pointers as to what else I might be missing would be very much
> > appreciated.
>
> I rather like more explicet joins, however this should work. Why do you
> coin categories though, if you only want the id from ptoc?
>
> I'd use this:
> SELECT
> p.products_id as v_products_id,
> p.products_model as v_products_model,
> p.products_image as v_products_image,
> p.products_price as v_products_price,
> p.products_weight as v_products_weight,
> p.products_date_added as v_date_avail,
> p.products_tax_class_id as v_tax_class_id,
> p.products_quantity as v_products_quantity,
> p.manufacturers_id as v_manufacturers_id,
> ptoc.categories_id as v_categories_id
> FROM products as p
> LEFT JOIN products_to_categories as ptoc
> ON p.products_id = ptoc.products_id
> WHERE p.products_model = '0775P21'
> --
> Rik Wasmus

Why have you changed the OP's implicict JOIN to an explicit LEFT JOIN?

#4 (**permalink**) 02-28-2008, 10:30 AM

On Tue, 04 Dec 2007 10:41:15 +0100, Captain Paralytic
<paul_lautman@yahoo.com> wrote:

> On 4 Dec, 02:26, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
>> On Tue, 04 Dec 2007 00:22:13 +0100, Nick Weisser
>> <nick.weis...@gmail.com>
>> wrote:
>> > Although all tables involved in this query seem to use indexes for the
>> > id fields, they somtimes take up to 6 seconds:
>> > # Time: 071204 0:05:37
>> > # Query_time: 5 Lock_time: 0 Rows_sent: 1 Rows_examined: 57035
>> > SELECT
>> > p.products_id as v_products_id,
>> > p.products_model as v_products_model,
>> > p.products_image as v_products_image,
>> > p.products_price as v_products_price,
>> > p.products_weight as v_products_weight,
>> > p.products_date_added as v_date_avail,
>> > p.products_tax_class_id as v_tax_class_id,
>> > p.products_quantity as v_products_quantity,
>> > p.manufacturers_id as v_manufacturers_id,
>> > subc.categories_id as v_categories_id
>> > FROM
>> > products as p,
>> > categories as subc,
>> > products_to_categories as ptoc
>> > WHERE
>> > p.products_id = ptoc.products_id AND
>> > p.products_model = '0775P21' AND
>> > ptoc.categories_id = subc.categories_id;
>>
>> > Any pointers as to what else I might be missing would be very much
>> > appreciated.
>>
>> I rather like more explicet joins, however this should work. Why do you
>> coin categories though, if you only want the id from ptoc?
>>
>> I'd use this:
>> SELECT
>> p.products_id as v_products_id,
>> p.products_model as v_products_model,
>> p.products_image as v_products_image,
>> p.products_price as v_products_price,
>> p.products_weight as v_products_weight,
>> p.products_date_added as v_date_avail,
>> p.products_tax_class_id as v_tax_class_id,
>> p.products_quantity as v_products_quantity,
>> p.manufacturers_id as v_manufacturers_id,
>> ptoc.categories_id as v_categories_id
>> FROM products as p
>> LEFT JOIN products_to_categories as ptoc
>> ON p.products_id = ptoc.products_id
>> WHERE p.products_model = '0775P21'
>
> Why have you changed the OP's implicict JOIN to an explicit LEFT JOIN?

Hmmm, good point, disregard the LEFT bit. I either just don't trust the
table enough for products always to be in categories, I assume he wants to
see products not in a category, or maybe I just shouldn't post 03:26 AM
local time...

--
Rik Wasmus

#5 (**permalink**) 02-28-2008, 10:30 AM

I was able to improve the slow query by simply adding another index to
the products table for the products_model field.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Slow queries problem