MySQL to XML performance (large amounts of data)
Posted: Thu Jun 12, 2014 11:18 pm
Hi,
I've been using a simple but reliable technique to display data from a MySQL database on a website.
I perform one query and then iterate through the result, adding it to a DOM structure that is saved as an XML file.
This has been working great in the past with small amounts of data, but I now need to extract hundreds of thousands of rows from the database.
I'm not really happy with the performance of this. The query takes negligible time, but the PHP script still takes 1.5mins on a cloud hosted server or 25sec on an in-house server.
tl;dr
*100,000 rows MySQL -> XML using PHP (single query, loop through results)
*Want to explore alternative methods for performance increases
UPDATES:
I've done some profiling and, contrary to my recollection, it turns out the query itself is taking a long time.
Query executes in 81ms using Sequel Pro (which isn't limiting the query), but when I time the PHP script the query takes 25sec. (connection is fast, query takes time, preparing xml is fast)
Why would the query be taking 25sec in PHP, but 81ms using Sequel Pro?
I've been using a simple but reliable technique to display data from a MySQL database on a website.
I perform one query and then iterate through the result, adding it to a DOM structure that is saved as an XML file.
This has been working great in the past with small amounts of data, but I now need to extract hundreds of thousands of rows from the database.
I'm not really happy with the performance of this. The query takes negligible time, but the PHP script still takes 1.5mins on a cloud hosted server or 25sec on an in-house server.
tl;dr
*100,000 rows MySQL -> XML using PHP (single query, loop through results)
*Want to explore alternative methods for performance increases
UPDATES:
I've done some profiling and, contrary to my recollection, it turns out the query itself is taking a long time.
Query executes in 81ms using Sequel Pro (which isn't limiting the query), but when I time the PHP script the query takes 25sec. (connection is fast, query takes time, preparing xml is fast)
Why would the query be taking 25sec in PHP, but 81ms using Sequel Pro?