Why Is The Same Query Very Quick From Within Phpmyadmin, And Very Slow Through A PHP Script?

[phdphd]

Hi All,
I am facing a situation where the exactly same query is very quick from within phpmyadmin (less than 1s)  and very slow through a php script (up to 2 minutes).
To obtain objective measures, in both cases, the query is executed only after restarting the computer, and turning the server on.
The environment is mysql 5.6.11/php 5.4.15.
This query returns just 33 records. It runs against a partitioned table and contains an inner join with another table.
The php test script structure is very simple :

$connexion = mysql_connect(...);
mysql_select_db('db', $connexion)
$time = microtime(TRUE);
$sql = 'SELECT distinct `field1`, ` field2` FROM `table1` partition (p1) Inner Join table2 On table1.id=table2.id Where table2.field1 = "something" And table2.field2 = " something_else"';
$rs = mysql_query($sql, $connexion);
echo '<pre>';
print_r(array('time elapsed' => microtime(TRUE) - $time));
echo '</pre>';
mysql_close($connexion);

Is there something wrong on PHP's side ? Thanks !

 

[PaulRyan]

A possible cause which I myself encountered, is instead of using "localhost" as the host, use "127.0.0.1" as the host.

[phdphd]

Putting "127.0.0.1" instead of "localhost" does not help. Also with "localhost", If I run the same query through a php script against a non-partitioned similar-size table, the query is very quick. There seems to be an issue with php dealing with partitioned tables.

Edited September 28, 2013 by phdphd

[mac_gyver]

it's not php that is dealing with the partitioned table. it's the database server. php only sends the query statement to the database server and gets the response back from the database server.

 

phpmyadmin is just a php script. if it operates differently from your php script, its likely because the phpmyadmin script is running on a different server from your web server or is configured differently. can you confirm if phpmyadmin script you are using is on the same server as your web server or on a different server?

 

also, can you check if phpmyadmin is using the mysql or mysqli database library?

 

is the code you posted your complete script involved, less any database credentials?

[jazzman1]

From MySQL's perspective, all of its incoming connections are coming from the same place. I think, you're running the same query statement onto two different DB servers.

[phdphd]

Yes, the PHP code I posted is the complete code. Actually it is just aimed at sending the query and measuring the time elapsed for processing the query. If I just change the query line to a query that runs against a non partitioned table but equivalent in size and in number of records to be returned, the same results are returned in a couple of seconds. My feeling is that when running against the partitioned table, all partitions are parsed, instead of the mentioned partition being directly queried.

 

In UwAmp, in PHP settings dialog box, both php_mysql.dll and php_mysqli.dll are enabled.

In phpmyadmin, in "Web Server" section, mysqli is mentioned as a PHP extension. The same section also indicates "Apache/2.2.22".

Also when I open http://localhost/partition_vs_non_partition/ page, "Apache/2.2.22 (Win32) PHP/5.4.15 Server at localhost Port 80" appears at the end of the page.

[AbraCadaver]

Change your mysql_*() functions to mysqli_*() and check again.

[Ch0cu3r]

I believe that mysqli (which standards for mysql imporovided) uses the mysqlnd driver which is more efficient than the the standard mysql extension. See if porting your code over to mysqli code base improves performance

[phdphd]

mysqli does not really help. I am not sure that php is the "guilty" part indeed. If I run the query in a standalone mode from within FlySpeed SQL Query, it still takes a very long time to run. So , so far, it just works as expected when executed from within PHPMyAdmin.

[vinny42]

Try adding "SQL_NO_CACHE" to the query that you are tunning in phpmadmin, I'l willing to bet that your query will start taking minutes there too:

 

SELECT SQL_NO_CACHE * FROM table WHERE... ;

 

The only other possibilities are that you are not running the queries on the same database, or you are using a prepared statement which causes the database not to use indexes.

Neither are very likely.

[phdphd]

As I said earlier to get objective results I always run the query after rebooting the computer and restarting the server, so that no previously cached data is available. I confirm that the query returns results immediately from within phpmyadmin.

Edited September 28, 2013 by phdphd

[vinny42]

 

 so that no previously cached data is available. I confirm that the query returns results immediately from within phpmyadmin.

 

The only way to guarantee that no cache is being used is by including SQL_NO_CACHE. Every other way is just assuming that no cache is being used, not guaranteeing.

 

Have you made sure that phpmyadmin is talking to the same database as the rest? Does it return the exact same data as the other tools?

[phdphd]

I confirm that with SQL_NO_CACHE and after computer rebooting+server starting, the results are returned immediately.

 

 

Have you made sure that phpmyadmin is talking to the same database as the rest? Does it return the exact same data as the other tools?

 

Yes. Yes.

[vinny42]

You can stop restarting the server, it makes no difference at all :-)

 

Are you sure the results are the same? Isn't PHPMyAdmin adding a LIMIT statement? (PHPMyAdmin is crap that way)

[phdphd]

Yes I am sure the results are the same, exactly 33 records in both cases (in the php script, I use a while loop with an echo line that displays the contents of the 2 fields record by record).

Edited September 28, 2013 by phdphd

[vinny42]

try running EXPLAIN on your query to see if the queryplans differ between the various methods.

[phdphd]

There are no differences in query plans between methods.

[vinny42]

Then you've got magic happening.

[AbraCadaver]

Then you've got magic happening.

 

Agreed.  Not just magic but FM.

 

PHPMyAdmin is PHP and either mysql or mysqli functions and so is your script.

[phdphd]

the magic happening for me would be getting the results through the php script as quick as from within phpmyadmin :-(

[jazzman1]

Try to execute the same by shell_exec() in php (just for a test), if you are on linux environment. I've never faced some kind of problem in my practice as developer before.

[phdphd]

Sorry, I am on windows.

[vinny42]

Can you post the EXLPAIN outputs of the fast and the slow queries? (please really post both, don't assume that they are identical)

[phdphd]

Here are the explain outputs, reformatted for better readability.

The first two lines represent the output in phpmyadmin, as displayed by phpmyadmin.

The last two lines represent the output as displayed in firefox, using a while loop in the php script with an echo line that displays the contents of each column. The only difference is that while the output from phpmyadmin displays "null" for the last field (Extra) of the second line, the output in firefox displays nothing.

 

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

 

1

SIMPLE

events

ref

PRIMARY,fk_events_cns1,city,cn_city

cn_city

334

const,const

3844

Using where; Using index; Using temporary

 

1

SIMPLE

event_crit

ref

fk_event_crit_events1,crit_pert

fk_event_crit_events1

4

mydb.events.event_id

21

NULL

 

 

 

 

 

 

 

 

 

 

 

 

1

SIMPLE

events

ref

 PRIMARY,fk_events_cns1,city,cn_city

 cn_city

334

const,const

3844

Using where; Using index; Using temporary

 

1

SIMPLE

event_crit

 ref

fk_event_crit_events1,crit_pert

fk_event_crit_events1

4

mydb.events.event_id

21

 

EDIT: Oops, seems the table formatting did not work. I hope the data are still relevant anyway.

Edited September 29, 2013 by phdphd

[mac_gyver]

Yes I am sure the results are the same, exactly 33 records in both cases (in the php script, I use a while loop with an echo line that displays the contents of the 2 fields record by record).

 

so, the code you posted isn't the complete and actual code, despite being asked and you confirming that it was. you do realize that it will take having all the relevant information about this problem in order to find the solution?

 

are you using the same database username in phpmyadmin as you are using in your php code?