phdphd

There are no differences in query plans between methods.

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).

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

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.

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.

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.

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.

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 !

And the winner is .... David ! The warning I get is of type "Cannot modify header information - headers already sent by (output started at C:\UwAmp\www\.....\page.php:XX) in C:\UwAmp\www\.....\page.php.php on line YY...." At XX level, there is print_r($_SESSION);, while YY corresponds to the line where the header('Location: page.php') is located. I solved the issue by setting output_buffering to On in the php.ini file. Thank you very much David!

To set them, $_SESSION['var_name']=var_value; To check them, echo '<pre>'; print_r($_SESSION); echo '</pre>';

Even with session_write_close(); inserted before the "header('Location: page.php');" statement, the session variables still get lost.

Hi All, I am facing a strange problem with session variables being partially lost after redirect. I recently moved from a wampserver/php 5.3.5 environment to a UwAmp/php 5.4.15 environment. I am using the same php files and scripts in both environments. In one of the scripts there are session variables being set and also a "header('Location: page.php');" statement. Everything works perfectly in the older environment. However, in the newer one, session variables belonging to the same script as the "header('Location: page.php');" statement are lost, while all other session variables previously set in other scripts are kept. The problem persists if I use a UwAmp/php 5.3.5 environment. Any idea of where the problem comes from ? Thanks!

another good piece of news !

Hi All, FYI I installed mysql 5.6 on an old 32-bit laptop and I am impressed by the power of partitioning in mysql 5.6, compared to partitioning in mysql 5.5. Despite the less powerful architecture (32 bits instead of 64 bits), select time has been divided by up to 3 (same table, same records). Now I've got a question to specialists : I noticed that performance is even greater on page reload (as fast as just 2/100s). It seems the results are cached somewhere. Let's assume that User1 connects to the page containing the php code that runs the query against the database. If a few seconds later User2 connects to the same page, thus running the same query, will User2 benefit from the results having been cached ? Thanks. BTW:I tried the "GROUP BY field1, field2" suggestion, but unfortunately without noticeable effects.

Actually docs say partitions are not compatible with innob tables that contain foreign keys. Since the table I want to partition will have a foreign key, I am afraid I will have to find a workaround.

Thanks kicken for your reply.

I do not have mysql 5.6 yet, and would appreciate some advice from people who might have already made some comparisons between the 2 versions.

Hi All, I have been doing some select tests on a partitioned table and on a non partitioned table, both having same data and around 4 millions records, and size of around 400 Mb each. Mysql version is 5.5.8. The partition targeted in the partitioned table contains around 300000 records, and the select statement is of type SELECT DISTINCT field1, field2 FROM table where partition_field3 =text_value Even if a select operation against the partitioned table is indeed much faster than against the non-partitioned table, I noticed that in 5-10% of the cases it takes a bit too long from the user's perspective. I was just wondering whether there is a significant difference in partition select performance between Mysql 5.5.8 and Mysql 5.6 since select operations against partitioned tables are not implemented the same way in both versions. In Mysql 5.6, the statement mentions the partition name rather than the "partitioned_column=value" criteria. Does it mean that a select statement against a Mysql 5.6 partitioned table would be somewhat as fast as the "classic" form of the same select statement against a non-partitioned table holding the same data as the partition? I would appreciate your opinion on that. I found no related benchmarks on the net. Thanks

Hi All, sorry for my late reply. This seems to be the key to my issue. By unsetting variables that are no longer needed at each step, I managed to recovery a significant amount of memory. Thank you Mac_gyver!

thanks for the info.

OK. From the example taken from http://dev.mysql.com I sucessfully added an id field then created a pk spanning both fields (id+city). I then run SHOW CREATE TABLE `customers_1` and got CREATE TABLE `customers_1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `first_name` varchar(25) DEFAULT NULL, `last_name` varchar(25) DEFAULT NULL, `street_1` varchar(30) DEFAULT NULL, `street_2` varchar(30) DEFAULT NULL, `city` varchar(15) NOT NULL DEFAULT '', `renewal` date DEFAULT NULL, PRIMARY KEY (`id`,`city`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 /*!50500 PARTITION BY LIST COLUMNS(city) (PARTITION pRegion_1 VALUES IN ('Oskarshamn','Högsby','Mönsterås') ENGINE = InnoDB, PARTITION pRegion_2 VALUES IN ('Vimmerby','Hultsfred','Västervik') ENGINE = InnoDB, PARTITION pRegion_3 VALUES IN ('Nässjö','Eksjö','Vetlanda') ENGINE = InnoDB, PARTITION pRegion_4 VALUES IN ('Uppvidinge','Alvesta','Växjo') ENGINE = InnoDB) */ What does "50500" stand for ?

Hi All, I am trying to partition an existing table with LIST COLUMNS partitioning. The structure of my statement is the following : alter table table_name PARTITION BY LIST COLUMNS(field_name) ( PARTITION p1 VALUES IN('value1'), PARTITION p2 VALUES IN(' value2') ) I get the message "A PRIMARY KEY must include all columns in the table's partitioning function". I do not understand this message. In the example below (successfully tested) taken from http://dev.mysql.com/doc/refman/5.5/en/partitioning-columns-list.htm it does not seem that the city column is a primary key. Actually there seems to be no primary key at all in the table definition. CREATE TABLE customers_1 ( first_name VARCHAR(25), last_name VARCHAR(25), street_1 VARCHAR(30), street_2 VARCHAR(30), city VARCHAR(15), renewal DATE ) PARTITION BY LIST COLUMNS(city) ( PARTITION pRegion_1 VALUES IN('Oskarshamn', 'Högsby', 'Mönsterås'), PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'Västervik'), PARTITION pRegion_3 VALUES IN('Nässjö', 'Eksjö', 'Vetlanda'), PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'Växjo') ); There must be something I did not grasp. Thanks for helping.

Thanks for your cooperation Guru. The page presents various groups of data to the user. Each time one group of data has been displayed I would like PHP to free the memory used and dedicate it to the display of the next group of data. When there is no much data to display, there is no problem. However I would like to avoid the "allowed memory size of XXXX bytes exhausted" message. Or may be an alternative could be implementing search boxes instead of groups that are likely to grow too much over time.

I added another php script with just "echo memory_get_usage();" in it. <?php echo memory_get_usage(); ?>

Hi all, I have a PHP script that connects to a db, retrieves some data and displays it to the user. To get an idea of memory usage, I inserted some "echo memory_get_usage();" statements at different stages at the very beginning of the script, after the execution of the query, after the creation of an array gathering the data retrieved, just before the end of script, and finally just after the end of script. As one may expect, the memory usage gets higher and higher as the script is running. However I do not undersand why the memory usage remains at its highest level at the last stage (after the end of the script), and does not get freed for further processing. Thanks for shedding some light.