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

[phdphd]

The code I initially posted was complete and actual code. I just changed it a bit when I was asked if I was sure the recordset was the same in both cases. I was 100% sure it was, but to satisfy the request I just added a while loop with an echo line to have the results displayed in the browser and have the visual proof that the recordset returned there was the same as the one returned in phpmyadmin.

 

Yes I use the same database username in phpmyadmin and in my php code.

Edited September 29, 2013 by phdphd

[Ch0cu3r]

Just trying to shred some more light on this

$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"';

Is that a true representation of your query. with table names being substituted for fieldX and tableX

 

Any reason for partition? As the alias p1 doesn't seem to be referenced anywhere in that query. Maybe posting the actual SQL query and dummy data will help further.

[phdphd]

Yes, this is a true representation of the query. However table1.fieldX does not represent the same data as table2.fieldX.

The reason for partition is that table1 contains 4 millions records, and the partition "only" about 400k.

[phdphd]

Hi All,

Me again, sorry . First of all I'd like to thank all of you for your patience and time dedicated to my issue, I really appreciate it, even if so far we have not found the solution. On my side I keep testing.

I've just noticed something strange : assuming that my query contains SQL_NO_CACHE and that I run it through the php script, the first execution will last very long (generally between 1 to 3 minutes). If I reload the php page, I noticed the execution lasts only one second. At first I thought the recordset could be cached somewhere else than in the query cache. So to clarify this I tried to make a slight change to my query : trying another partition. And against the new partition, the query ran as quick as when reloading. It is like if on the first execution the query wanted to "make acquaintance" (sort of indexing?) with all of the partitions. I would like to know your opinion on this. Thanks.

[kicken]

Mysql may be loading all the indexes and other data into memory caches (different from the query cache). Once loaded then queries can use those cache to search for data rather than accessing the disk for everything. PHPMyAdmin may be causing this to already happen by querying for all the table details and what not before you get to the screen where you finally run your query.

[phdphd]

Very interesting. I am going to investigate key caching. I think I understand why in PHPMyAdmin even the first execution is fast. Actually I think it is just an illusion, in the sense that the time that the php script takes to gather all the indexes is apparently taken by PHPMyAdmin when one selects the database (by clicking on its name) before running the query manually under the SQL tab.

[vinny42]

 

 PHPMyAdmin may be causing this to already happen by querying for all the table details and what not before you get to the screen where you finally run your query. 

 

Very good point, phpmyadmin may be warming the cache up. The filesystem will also have cached the data regardless of what MySQL caches (which is why other databases don't have a recordcache at all)