473,326 Members | 2,182 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Exporting MySQL Data - timeout help

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
Jun 2 '08 #1
1 1908
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
Jun 2 '08 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Andy Levy | last post by:
Hi there is a function in phpmyadmin that exports all the data etc from a mysql database to an sql text document, or Excel File. I would like to know if there is an inherent way of doing this...
0
by: Lenz Grimmer | last post by:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, MySQL 4.0.14, a new version of the popular Open Source/Free Software Database, has been released. It is now available in source and binary...
0
by: I.P. | last post by:
No one has replied to my post. ----- Original Message ----- From: "I.P." <jancio_wodnik@wp.pl> To: <mysql@lists.mysql.com> Sent: Monday, August 18, 2003 1:01 PM Subject: mysql 4.0.14 +...
2
by: James Alexander Starritt | last post by:
I also posted similarly in mailing.database.mysql I have created a rather large (60 table database) website dealio in PHP that works with MS SQL Server, Oracle, MySQL and presumably any other...
1
by: jlee | last post by:
I'm pretty much a newbie on mysql, and I need some help. I am running mysql Ver 12.22 Distrib 4.0.24, for portbld-freebsd5.4 (i386) on a server hosting an active website. The site's developer...
1
by: Ike | last post by:
Recently, I began using a different MySQL verver (i.e. different machine as well as different version#, going from 4.12a to 4.1.9 max). The following query used to work: select firstname,...
0
by: cwho.work | last post by:
Hi! We are using apache ibatis with our MySQL 5.0 database (using innodb tables), in our web application running on Tomcat 5. Recently we started getting a number of errors relating to...
0
by: rhepsi | last post by:
Dear All, Im vb.net 1.1, When im trying to update the records from mysql to postgresql, the connection error: the following code is Update data button click: Dim myPgrConnect As...
3
by: rockdale | last post by:
Hi, all: My web application using MS EntLib for .net 2.0 (Jan 2006) to access my backend database. It works fine with MS SQL 2k. Now we are migrate from MS SQL to mySQL. Everything looks fine...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.