[SOLVED] Exporting MySQL Data - timeout help

[cmgmyr]

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. 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???

 

Thanks in advance,

-Chris

[cmgmyr]

Anyone?

[DarkWater]

If it's timing out, just change the timeout length of the script...

[cmgmyr]

I know that I can do that, but I was looking for a more efficient way to handle this query. If there isn't a solution that way I can change the timeout as a last resort. I don't really want the client waiting 5 min to download/upload and XL file though.

[DarkWater]

If it's a large database, there's not much you can do.

[cmgmyr]

It's about a medium size, there is about 1500 products in there right now.

[DarkWater]

There's no way to pull that entire query off and export the information in the default 30 second timeout. 

[cmgmyr]

I know, I have it at 3 minutes right now. Even running it directly in phpMyAdmin takes 4-5 min usually.

[DarkWater]

There's honestly no way to optimize that query to run in 30 seconds, so you'd be better off setting a high timeout time and changing the Apache timeout directive.

[cmgmyr]

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.

[DarkWater]

Oh, I figured you had indexed them already to try and optimize the time.  Just be aware that inserting data will be slower now because MySQL needs to reindex every column every single time you insert a row. >_>

[cmgmyr]

No problem. Sometimes in the big projects you forget the "little" things until there is a problem. Lesson learned!