473,585 Members | 2,514 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_extend ed 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 1925
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_extend ed 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
2082
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 without phpmyadmin. - straight through php. thank you
0
3513
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 form for a number of platforms from our download pages at http://www.mysql.com/downloads/ and mirror sites.
0
2129
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 + replication + windows XP PROF Hi, it's my story.
2
3076
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 data base I add to the wrapper functions that I created. All the development was done in Oracle, but now its time to create that large datastructure...
1
3366
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 uses his own php shopping cart to receive customer orders. The configuration was done via cPanel with no external modifications - which produced...
1
15375
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, lastname, from associates where username like 'nancianne' but now fails with: "You have an error in your SQL syntax; check the manual that corresponds...
0
11694
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 java.sql.SQLException: Deadlock found when trying to get lock; Try restarting transaction message from server: "Lock wait timeout exceeded; try restarting...
0
1256
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 New NpgsqlConnection Dim myPgrCommand As New NpgsqlCommand
3
3572
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 except one serious problem: we got the "too many connections" error. Looks like the EntLib did not properly close these connections. on mySQL...
0
7835
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8195
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7947
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
1
5707
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5386
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3831
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3856
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2340
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1444
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.