phdphd

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.

Hi All, On my webpage I have a div that scrolls vertically, with a list of items in it. The items in the div come from a php array. I noticed that the building of the div gets slow when there are many items in the array. Since the user might not be interested by the contents of the div, I am considering filling the div by only adding items to it each time the user scrolls down to the end of the div. So basically, each time the user would reach the bottom of the div, the JS code would add items from the php array to the list in the div. Is it possible to implement this ? Thanks.

Hi all, I am trying to create an event, that does an insert if a condition is met. For this insert, I initially wanted to set some locks. Unfortulately, I get a message saying " LOCK is not allowed in stored procedures". So I redesigned the event as follows. Would this work ? If so, would the LOCK IN SHARE MODE statement apply until after the IF (blablabla > 0) structure has been processed ? CREATE DEFINER=`root`@`localhost` EVENT `event_name` ON SCHEDULE EVERY 1 MINUTE STARTS '2013-08-10 22:31:56' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN DECLARE blablabla DECLARE blablabla DECLARE ... SET blablabla SET blablabla SET ... IF (condition_met) THEN SELECT SQL_CALC_FOUND_ROWS .....from one table... LOCK IN SHARE MODE; SELECT FOUND_ROWS( ) into blablabla; IF (blablabla > 0) THEN INSERT INTO ........another table.....; END IF ; END IF ; END Thanks in advance for your help!

Thanks !

In the last example, the result I am looking for is : [countries] => Array ( [0] => Spain [1] => France [3] => France [4] => USA ) [cities] => Array ( [0] => Madrid [1] => Paris [3] => Bordeaux [4] => Boston ) In other words, only [2] => France and [2] => Paris are removed because they represent the only pair of country/city values that appears more than once.

Sorry, my starting arrays were too simple. Let assume the following starting arrays instead. In this example, applying array_unique to each array would result in a 3-value array for countries and in a 4-value array for cities. [countries] => Array ( [0] => Spain [1] => France [2] => France [3] => France [4] => USA ) [cities] => Array ( [0] => Madrid [1] => Paris [2] => Paris [3] => Bordeaux [4] => Boston )

Hi All, I'm wondering whether there is a quick way to go from [countries] => Array ( [0] => Spain [1] => France [2] => France [3] => USA ) [cities] => Array ( [0] => Madrid [1] => Paris [2] => Paris [3] => Boston ) to [countries] => Array ( [0] => Spain [1] => France [3] => USA ) [cities] => Array ( [0] => Madrid [1] => Paris [3] => Boston ) Actually, it is not important that keys are preserved. Thanks a lot !

Shame on me ! It does work ! Just forgot to test il with CALL...

Hi All, I do not understand why the following stored procedure does not work. DELIMITER $$ DROP PROCEDURE IF EXISTS `proc` $$ CREATE PROCEDURE `proc` ( ) BEGIN DECLARE result LONGTEXT; SET SESSION group_concat_max_len =4294967295; SET result = (SELECT GROUP_CONCAT( DISTINCT ids ORDER BY ids ASC SEPARATOR ',' ) FROM table1); INSERT INTO `table2 ` (`id`, `concat`) VALUES (NULL, result); END $$ DELIMITER ; When I run it, I get no error, but the insert instruction seems ignored. This insert instruction bears a variable, but even though I use a hard-coded value in it instead of the "result" variable, it still gets ignored (whereas it would work if executed alone). Please note that when executed alone the SELECT GROUP_CONCAT instruction DOES return a concatenation. Thanks for your help!

Yes, the ID column is the primary key, and it is the only one there.

I noticed that PhpMyAdmin reports a processing time of 0.0246 sec. But actually it displays the first set of rows only after more or less 15 seconds. The Explain reports the following : id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE ma_table ALL PRIMARY NULL NULL NULL 8211 Using where (If I run the same query without the IN clause, both the processing and the rendering are immediate.)

Let me rephrase the context of my issue. I have a PHP processing that ends with the building of a series of ids. Then there is a SQL query based on this series of ids, that I run against a table with an Id column, in order to get all the data in rows matching the ids provided.

An index is already created. As for "self-joining", this approach seems suggested here : http://stackoverflow.com/questions/10023117/select-where-in-subquery-slow

Hi All, I use a query that gets rows according to specified ids. The typical syntax of the query is as follows: SELECT * FROM table WHERE id IN(1,2,3,....,100,...) The query is very slow (more than 10 seconds) if the IN clause contains thousands of ids. (The same query without the IN clause runs instantaneously.) Note that the number of ids is random and depends of choices from the user. I read here and there than using an "inner join" might be a solution. I would need some help in converting this query into an inner join-based query. Thanks a lot.

Thank you very much mac_gyver!