Re: Exporting MySQL Data - timeout help

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

On Apr 24, 11:50*am, Rik Wasmus <luiheidsgoe...@hotmail.com> wrote:
> 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_extendedAS 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- Hide quoted text -
>
> - Show quoted text -

Thanks for the idea, I did that and I found a couple things. I went
through the tables in the query and "indexed" all of the connecting
columns that weren't already indexed or a key, now it runs in about 5
seconds.

Thanks for the help!