Hey All,
I'm having a little problem with exporting data from my database. The
problem is that the query seems to be too much for PHP and MySQL to
handle since it is timing out. I have the timeout set right now at 3
minutes. I can run this in phpMyAdmin and it usually takes 4-5 minuted
to complete. I do not want to have to make the client wait that long
for this. This is part of an import/export Excel function that I have
made. Here is the query:

SELECT p.id, p.base_no, p.style_no, t1.name AS category1, t2.name as
category2, t3.name as category3, p.metal, p.description, c.name as
collection, p.price, pe.b2c_desc, pe.meta_title, pe.meta_desc,
pe.meta_keys, pe.alt_tag, p.site
FROM
categories AS t1
LEFT JOIN
categories AS t2 ON t2.parentid = t1.id
LEFT JOIN
categories AS t3 ON t3.parentid = t2.id
INNER JOIN
products AS p ON t3.id = p.categories_id
LEFT JOIN
collections AS c ON p.collection = c.id
LEFT JOIN
products_extended AS pe ON p.style_no = pe.style_no

The categories table is a hierarchical setup (id, parentid, name). Do
you guys have any ideas about how to make this function better? Any
other solutions??? How do you export data from your databases like
this?

Thanks in advance,
-Chris

cmgmyr wrote:
> Hey All,
> I'm having a little problem with exporting data from my database. The
> problem is that the query seems to be too much for PHP and MySQL to
> handle since it is timing out. I have the timeout set right now at 3
> minutes. I can run this in phpMyAdmin and it usually takes 4-5 minuted
> to complete. I do not want to have to make the client wait that long
> for this. This is part of an import/export Excel function that I have
> made. Here is the query:
>
> SELECT p.id, p.base_no, p.style_no, t1.name AS category1, t2.name as
> category2, t3.name as category3, p.metal, p.description, c.name as
> collection, p.price, pe.b2c_desc, pe.meta_title, pe.meta_desc,
> pe.meta_keys, pe.alt_tag, p.site
> FROM
> categories AS t1
> LEFT JOIN
> categories AS t2 ON t2.parentid = t1.id
> LEFT JOIN
> categories AS t3 ON t3.parentid = t2.id
> INNER JOIN
> products AS p ON t3.id = p.categories_id
> LEFT JOIN
> collections AS c ON p.collection = c.id
> LEFT JOIN
> products_extended AS pe ON p.style_no = pe.style_no
>
> The categories table is a hierarchical setup (id, parentid, name). Do
> you guys have any ideas about how to make this function better? Any
> other solutions??? How do you export data from your databases like
> this?

If your query takes that long, do an EXPLAIN on it, and ask the good
people at comp.databases,mysql why this takes so long.

f'up comp.databases.mysql
--
Rik Wasmus