Hi,
I have an extremely large MySQL database (about 1 million items) and am running a fairly complex query on that database. It takes about 2 seconds when I enter the SQL command into phpMyAdmin but it takes around 1-2 minutes to generate that same output using PHP code.
Why would there be such a time difference? If it was a poorly executed query then wouldn't it take a long time for the results to appear in phpMyAdmin as well?
It's very possible that the PHP script does something in addition to simply executing the query (such as shuffling tremendous amounts of data back and forth.) Could you show us the PHP code that executes your SQL?
Note that if you haven't got output buffering on, and output the data piece by piece to the client, that'll slow you down a lot since it sends so many small chunks to the client, instead of one big chunk. Have a look at output buffering in PHP.
In its simplest form:
ob_start();
// Output lots of data here.
ob_end_flush();
phpMyAdmin is optimized to handle large amounts of data. Have a look at its internal code to see how it fetches data from a query, it could be of some help.
Also, I'm assuming you've got phpMyAdmin on the same server as your script?
Are you sure you are getting exactly the same output ?
If you have tons on data in your database, how many lines does your query return ?
When you launch a query with phpMyAdmin, it automatically add pagination.
So, with this initial query :
select *
from test
phpMyAdmin actually executes this one :
select *
from test
limit 0, 30
If you don't have that limit in your query (when executing it from your PHP script), it could explain the difference : fetching 30 lines and fetching hundreds/thousands/more lines does not take the same amount of time (same thing for rendering those).
If you are using limit in your query and/or don't try to get lots of lines, can you post your code ?
(Another possibile reason would be query cache : first time the query is run, it takes lots of time ; next times, MySQL has kept the results in cache)