MyISAM vs InnoDB - Index choice and Huge performance difference
Hi everybody, I have a MySQL database with MyISAM tables. As we're experiencing a lot of locking-related problems I decided to migrate to InnoDB. Our database is composed by a lot of small tables (1.000 - 10.000 rows) and a huge table containing 7.000.000 rows, this big table is a sort of a log of our subscriber's phone calls. I have a query I often run on the big table that is performing really poorly on InnoDB (18mins Innodb vs 29secs MyISAM) This is my query SELECT DATE_FORMAT(calldate, '%d') AS day, count(*) AS num, disposition FROM cdr WHERE calldate BETWEEN '2007-07-01 00:00:00' AND '2007-07-30 23:59:59' GROUP BY day, disposition; Using EXPLAIN I see that the query on the InnoDB table isn't using indexes at all but the one on MyISAM table (same structure, same indexes, same data) is choosing the correct index. Here are my EXPLAIN results MyISAM: id: 1 select_type: SIMPLE table: cdr type: range possible_keys: calldate,date-context-cause key: calldate key_len: 8 ref: NULL rows: 697688 Extra: Using where; Using temporary; Using filesort Innodb: id: 1 select_type: SIMPLE table: cdr_innodb type: ALL possible_keys: calldate,date-context-cause key: NULL key_len: NULL ref: NULL rows: 5035407 Extra: Using where; Using temporary; Using filesort As you can see, Innodb doesn't use the calldate index (which seems to me the correct choice) Probably I can solve this query performance problem with an index on calldate, disposition but I'd like to understand deeper the causes of that to avoide re-analizing every query ad retry to optimize it as I did with MyISAM. I have got a Xeon quad core with SAS disks and 4 GB of RAM I'm using a config file taken from MySQL sources optimized for innodb and 4G RAM (my-innodb-heavy-4G.cnf) I followed some simple optimization rules as putting InnoDB data dir on a different array of disks on a different channel, etc... Im using MySQL 5.0.32 on a Debian stable. Tnx in advance for help Regards Edoardo Serra WeBRainstorm S.r.l. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incrementing a Private Integer Space
David T. Ashley wrote: I have a table with two integer fields (call them p and q). When I insert a record with a known p, I want to choose q to be one larger than the largest q with that p. What is the best and most efficient way to do this? For example, let's say the table contains (p,q): 1,1 1,2 1,3 2,1 2,2 2,3 2.4 2,5 3,1 3,2 If I insert a new record with p=2, I would want to choose q to be 6. But if I insert a record with p=3, I would want to choose q to be 3. Is there any alternative to locking the table, querying for max q with the desired p, then inserting? insert into test1 set p = 2, q = (select max(q) + 1 from test1 as tmp where p = 2) Probably not very efficient, but it works. -Stut -- http://stut.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incrementing a Private Integer Space
On 11/25/07, Stut [EMAIL PROTECTED] wrote: David T. Ashley wrote: I have a table with two integer fields (call them p and q). When I insert a record with a known p, I want to choose q to be one larger than the largest q with that p. What is the best and most efficient way to do this? For example, let's say the table contains (p,q): 1,1 1,2 1,3 2,1 2,2 2,3 2.4 2,5 3,1 3,2 If I insert a new record with p=2, I would want to choose q to be 6. But if I insert a record with p=3, I would want to choose q to be 3. Is there any alternative to locking the table, querying for max q with the desired p, then inserting? insert into test1 set p = 2, q = (select max(q) + 1 from test1 as tmp where p = 2) Probably not very efficient, but it works. -Stut -- http://stut.net/ Thanks for the help, Stut. Is there any way to modify the query so that it will also work on the first insert where there are no records with the specified p? (I.e. imagine in the table below that I wanted to insert with p=25 ... and I'd want the query to insert 25,1.) Thanks, Dave.
Re: Incrementing a Private Integer Space
David T. Ashley wrote: On 11/25/07, Stut [EMAIL PROTECTED] wrote: David T. Ashley wrote: I have a table with two integer fields (call them p and q). When I insert a record with a known p, I want to choose q to be one larger than the largest q with that p. What is the best and most efficient way to do this? For example, let's say the table contains (p,q): 1,1 1,2 1,3 2,1 2,2 2,3 2.4 2,5 3,1 3,2 If I insert a new record with p=2, I would want to choose q to be 6. But if I insert a record with p=3, I would want to choose q to be 3. Is there any alternative to locking the table, querying for max q with the desired p, then inserting? insert into test1 set p = 2, q = (select max(q) + 1 from test1 as tmp where p = 2) Probably not very efficient, but it works. -Stut -- http://stut.net/ Thanks for the help, Stut. Is there any way to modify the query so that it will also work on the first insert where there are no records with the specified p? (I.e. imagine in the table below that I wanted to insert with p=25 ... and I'd want the query to insert 25,1.) Thanks, Dave. You could probably do something using the if function. Untested but should work... insert into test1 set p = 4, q = if((select count(1) from test1 as tmp1 where p = 4) 0, (select max(q) + 1 from test1 as tmp where p = 4), 1) -Stut -- http://stut.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incrementing a Private Integer Space
Stut wrote: insert into test1 set p = 2, q = (select max(q) + 1 from test1 as tmp where p = 2) Probably not very efficient, but it works. -Stut Auto increment is much easier to do. If your primary key is made up of two fields and one of them is Auto Increment, then it will have the desired behavior, just do this experiment and see CREATE TABLE `t` ( `p` int(10) unsigned NOT NULL default '0', `q` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`p`,`q`) ) ; INSERT INTO `t` (`p`,`q`) VALUES (1,NULL), (1,NULL), (1,NULL), (2,NULL), (2,NULL), (2,NULL), (2,NULL), (2,NULL), (3,NULL), (3,NULL); -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Ham Radio Repeater Database. http://hrrdb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incrementing a Private Integer Space
On 11/25/07, Chris W [EMAIL PROTECTED] wrote: Stut wrote: insert into test1 set p = 2, q = (select max(q) + 1 from test1 as tmp where p = 2) Probably not very efficient, but it works. -Stut Auto increment is much easier to do. If your primary key is made up of two fields and one of them is Auto Increment, then it will have the desired behavior, just do this experiment and see CREATE TABLE `t` ( `p` int(10) unsigned NOT NULL default '0', `q` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`p`,`q`) ) ; INSERT INTO `t` (`p`,`q`) VALUES (1,NULL), (1,NULL), (1,NULL), (2,NULL), (2,NULL), (2,NULL), (2,NULL), (2,NULL), (3,NULL), (3,NULL); -- Chris W KE5GIX Hi Chris, OK, I will try that. Thanks for the help. My assumption in reading your original post was that you didn't understand what I wanted to do (but you did). In order for me to use the solution you have proposed, in addition to working, this behavior would have to be described in the MySQL manual. The reason is that this seems to be the kind of behavior that could change from version to version. BEGIN IMPORTANT POINT I don't suppose you know the section in the manual that defines the behavior you're describing? END IMPORTANT POINT The issue is that unless this behavior is defined, changing autoincrement from the behavior you described to a simpler version that just uses ascending integers with no other context is the kind of thing where the guys at MySQL might reason that it won't affect anyone or wasn't defined in a specific way anyway. Strictly speaking, this feared change wouldn't affect the logical correct operation of my database (there would still be key uniqueness), but the neat n, n+1, n+2 ordering I'm looking for in q would confuse humans. Phrased more compactly: unless MySQL calls out this behavior in the documentation, your solution scares the snot out of me. Thank you sincerely, Dave.
Re: Incrementing a Private Integer Space
David T. Ashley wrote: Hi Chris, OK, I will try that. Thanks for the help. My assumption in reading your original post was that you didn't understand what I wanted to do (but you did). In order for me to use the solution you have proposed, in addition to working, this behavior would have to be described in the MySQL manual. The reason is that this seems to be the kind of behavior that could change from version to version. BEGIN IMPORTANT POINT I don't suppose you know the section in the manual that defines the behavior you're describing? END IMPORTANT POINT From '3.6.9. Using AUTO_INCREMENT' For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiplecolumn index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups. I didn't know it only worked in MyISAM and BDB... I almost always use MyISAM anyway. However I don't use that feature anymore due to my change in thinking on primary keys. The only time I use a primary key that has more than one field is if the table is a many to many relationship table and in that case neither field is auto increment. -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Ham Radio Repeater Database. http://hrrdb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Incrementing a Private Integer Space
Dave is trying to establish an algorithm which would fit your requirement I can see column q is auto-increment which makes sense as you retain the capability to generate a unique row but jumping into the middle of a conversation without knowing the prior discussionWhat is/was/will be the purpose of column p..?Can we denormalise a bit and extrapolate the value of column p based on known value of column q?Martin-__Disclaimer and confidentiality noteEverything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. On 11/25/07, Chris W [EMAIL PROTECTED] wrote: Stut wrote: insert into test1 set p = 2, q = (select max(q) + 1 from test1 as tmp where p = 2) Probably not very efficient, but it works. -StutAuto increment is much easier to do. If your primary key is made up of two fields and one of them is Auto Increment, then it will have the desired behavior, just do this experiment and see CREATE TABLE `t` ( `p` int(10) unsigned NOT NULL default '0', `q` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`p`,`q`) ) ; INSERT INTO `t` (`p`,`q`) VALUES (1,NULL), (1,NULL), (1,NULL), (2,NULL), (2,NULL), (2,NULL), (2,NULL), (2,NULL), (3,NULL), (3,NULL); -- Chris W KE5GIX Hi Chris, OK, I will try that. Thanks for the help. My assumption in reading your original post was that you didn't understand what I wanted to do (but you did). In order for me to use the solution you have proposed, in addition to working, this behavior would have to be described in the MySQL manual. The reason is that this seems to be the kind of behavior that could change from version to version. BEGIN IMPORTANT POINT I don't suppose you know the section in the manual that defines the behavior you're describing? END IMPORTANT POINT The issue is that unless this behavior is defined, changing autoincrement from the behavior you described to a simpler version that just uses ascending integers with no other context is the kind of thing where the guys at MySQL might reason that it won't affect anyone or wasn't defined in a specific way anyway. Strictly speaking, this feared change wouldn't affect the logical correct operation of my database (there would still be key uniqueness), but the neat n, n+1, n+2 ordering I'm looking for in q would confuse humans. Phrased more compactly: unless MySQL calls out this behavior in the documentation, your solution scares the snot out of me. Thank you sincerely, Dave. _ Put your friends on the big screen with Windows Vista® + Windows Live™. http://www.microsoft.com/windows/shop/specialoffers.mspx?ocid=TXT_TAGLM_CPC_MediaCtr_bigscreen_102007
Re: Incrementing a Private Integer Space
Hi Martin, The easiest way to restore context in this conversation is to go to the MySQL home page (www.mysql.com), then go to Community, then Lists, then to look at the archives of the main MySQL mailing list (this one). I believe at this point that Chris and Stut answered my question decisively. They both gave me single-query methods of achieving the behavior that I want. Thanks to all ... Dave. On 11/25/07, Martin Gainty [EMAIL PROTECTED] wrote: Dave is trying to establish an algorithm which would fit your requirement I can see column q is auto-increment which makes sense as you retain the capability to generate a unique row but jumping into the middle of a conversation without knowing the prior discussionWhat is/was/will be the purpose of column p..?Can we denormalise a bit and extrapolate the value of column p based on known value of column q?Martin-__Disclaimer and confidentiality noteEverything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. On 11/25/07, Chris W [EMAIL PROTECTED] wrote: Stut wrote: insert into test1 set p = 2, q = (select max(q) + 1 from test1 as tmp where p = 2) Probably not very efficient, but it works. -StutAuto increment is much easier to do. If your primary key is made up of two fields and one of them is Auto Increment, then it will have the desired behavior, just do this experiment and see CREATE TABLE `t` ( `p` int(10) unsigned NOT NULL default '0', `q` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`p`,`q`) ) ; INSERT INTO `t` (`p`,`q`) VALUES (1,NULL), (1,NULL), (1,NULL), (2,NULL), (2,NULL), (2,NULL), (2,NULL), (2,NULL), (3,NULL), (3,NULL); -- Chris W KE5GIX Hi Chris, OK, I will try that. Thanks for the help. My assumption in reading your original post was that you didn't understand what I wanted to do (but you did). In order for me to use the solution you have proposed, in addition to working, this behavior would have to be described in the MySQL manual. The reason is that this seems to be the kind of behavior that could change from version to version. BEGIN IMPORTANT POINT I don't suppose you know the section in the manual that defines the behavior you're describing? END IMPORTANT POINT The issue is that unless this behavior is defined, changing autoincrement from the behavior you described to a simpler version that just uses ascending integers with no other context is the kind of thing where the guys at MySQL might reason that it won't affect anyone or wasn't defined in a specific way anyway. Strictly speaking, this feared change wouldn't affect the logical correct operation of my database (there would still be key uniqueness), but the neat n, n+1, n+2 ordering I'm looking for in q would confuse humans. Phrased more compactly: unless MySQL calls out this behavior in the documentation, your solution scares the snot out of me. Thank you sincerely, Dave. _ Put your friends on the big screen with Windows Vista(R) + Windows Live™. http://www.microsoft.com/windows/shop/specialoffers.mspx?ocid=TXT_TAGLM_CPC_MediaCtr_bigscreen_102007
MySQL 5.1.22 and Greek Language
Hi, I am having a problem with Greek character set in MySQL 5.1.22 in Windows XP where i am trying to have a column with greek and another one with english text in the same table. I am setting through the MySQL Query Browser tool the character set for each column and in Command Line to greek and i also set the database to have the default character set to greek. What ever i do i get instead of greek text... This of course also affects results from queries executed within PHP. I have seen similar posts while searching in google but whatever i tried it didn't work. How can this be resolved? Before i migrate to MySQL 5.1.22 I was using 4.1.22 and I had no problem. Thanks in advance -- Spiros P.
Replication vs. mysql-table-sync
Is mysql-table-sync design to be used as a fix for when your replication is out of sync OR can it be used instead of replication? Thanks, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB - Index choice and Huge performance difference
just want to take a note on 4Gbytes What kernel u use? 4Gbytes or bigger means nothing on your MySQL, because if your kernel is not compiled using correct patch or simply use CentOS/RHEL, then your MySQl will limited to use up to 2Gbytes only, so 4Gbytes -- 2Gbytes is useless On 11/25/07, Edoardo Serra [EMAIL PROTECTED] wrote: Hi everybody, I have a MySQL database with MyISAM tables. As we're experiencing a lot of locking-related problems I decided to migrate to InnoDB. Our database is composed by a lot of small tables (1.000 - 10.000 rows) and a huge table containing 7.000.000 rows, this big table is a sort of a log of our subscriber's phone calls. I have a query I often run on the big table that is performing really poorly on InnoDB (18mins Innodb vs 29secs MyISAM) This is my query SELECT DATE_FORMAT(calldate, '%d') AS day, count(*) AS num, disposition FROM cdr WHERE calldate BETWEEN '2007-07-01 00:00:00' AND '2007-07-30 23:59:59' GROUP BY day, disposition; Using EXPLAIN I see that the query on the InnoDB table isn't using indexes at all but the one on MyISAM table (same structure, same indexes, same data) is choosing the correct index. Here are my EXPLAIN results MyISAM: id: 1 select_type: SIMPLE table: cdr type: range possible_keys: calldate,date-context-cause key: calldate key_len: 8 ref: NULL rows: 697688 Extra: Using where; Using temporary; Using filesort Innodb: id: 1 select_type: SIMPLE table: cdr_innodb type: ALL possible_keys: calldate,date-context-cause key: NULL key_len: NULL ref: NULL rows: 5035407 Extra: Using where; Using temporary; Using filesort As you can see, Innodb doesn't use the calldate index (which seems to me the correct choice) Probably I can solve this query performance problem with an index on calldate, disposition but I'd like to understand deeper the causes of that to avoide re-analizing every query ad retry to optimize it as I did with MyISAM. I have got a Xeon quad core with SAS disks and 4 GB of RAM I'm using a config file taken from MySQL sources optimized for innodb and 4G RAM (my-innodb-heavy-4G.cnf) I followed some simple optimization rules as putting InnoDB data dir on a different array of disks on a different channel, etc... Im using MySQL 5.0.32 on a Debian stable. Tnx in advance for help Regards Edoardo Serra WeBRainstorm S.r.l. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Regards, Ady Wicaksono Email: ady.wicaksono at gmail.com http://adywicaksono.wordpress.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MyISAM vs InnoDB - Index choice and Huge performance difference
U might want to try seting you index to calldate, disposition -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Sunday, November 25, 2007 10:03 PM To: Edoardo Serra Cc: mysql@lists.mysql.com Subject: Re: MyISAM vs InnoDB - Index choice and Huge performance difference just want to take a note on 4Gbytes What kernel u use? 4Gbytes or bigger means nothing on your MySQL, because if your kernel is not compiled using correct patch or simply use CentOS/RHEL, then your MySQl will limited to use up to 2Gbytes only, so 4Gbytes -- 2Gbytes is useless On 11/25/07, Edoardo Serra [EMAIL PROTECTED] wrote: Hi everybody, I have a MySQL database with MyISAM tables. As we're experiencing a lot of locking-related problems I decided to migrate to InnoDB. Our database is composed by a lot of small tables (1.000 - 10.000 rows) and a huge table containing 7.000.000 rows, this big table is a sort of a log of our subscriber's phone calls. I have a query I often run on the big table that is performing really poorly on InnoDB (18mins Innodb vs 29secs MyISAM) This is my query SELECT DATE_FORMAT(calldate, '%d') AS day, count(*) AS num, disposition FROM cdr WHERE calldate BETWEEN '2007-07-01 00:00:00' AND '2007-07-30 23:59:59' GROUP BY day, disposition; Using EXPLAIN I see that the query on the InnoDB table isn't using indexes at all but the one on MyISAM table (same structure, same indexes, same data) is choosing the correct index. Here are my EXPLAIN results MyISAM: id: 1 select_type: SIMPLE table: cdr type: range possible_keys: calldate,date-context-cause key: calldate key_len: 8 ref: NULL rows: 697688 Extra: Using where; Using temporary; Using filesort Innodb: id: 1 select_type: SIMPLE table: cdr_innodb type: ALL possible_keys: calldate,date-context-cause key: NULL key_len: NULL ref: NULL rows: 5035407 Extra: Using where; Using temporary; Using filesort As you can see, Innodb doesn't use the calldate index (which seems to me the correct choice) Probably I can solve this query performance problem with an index on calldate, disposition but I'd like to understand deeper the causes of that to avoide re-analizing every query ad retry to optimize it as I did with MyISAM. I have got a Xeon quad core with SAS disks and 4 GB of RAM I'm using a config file taken from MySQL sources optimized for innodb and 4G RAM (my-innodb-heavy-4G.cnf) I followed some simple optimization rules as putting InnoDB data dir on a different array of disks on a different channel, etc... Im using MySQL 5.0.32 on a Debian stable. Tnx in advance for help Regards Edoardo Serra WeBRainstorm S.r.l. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Regards, Ady Wicaksono Email: ady.wicaksono at gmail.com http://adywicaksono.wordpress.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.503 / Virus Database: 269.16.6 - Release Date: 11/24/2007 12:00 AM No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.503 / Virus Database: 269.16.6 - Release Date: 11/24/2007 12:00 AM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with a Procedure
Hi! I must say I cant see how this would help me. I know the tabels isnt empty since when running in query browsern I get a result. There must be something else thats wrong.. ...but thanks anyway! /Hylsan On Nov 23, 2007 4:43 PM, Martin Gainty [EMAIL PROTECTED] wrote: Tomas- I would effect a quick iterative check on the table(s) to see if they are empty e.g. SELECT count(trans2.nettovikt) from trans2; (If recordcount0) then SELECT SUM(trans2.nettovikt) FROM trans2 INNER JOIN artikel on trans2.artikel=artikel.artikel (If recordcount0) then SELECT SUM(trans2.nettovikt) INTO ut_summa FROM trans2 INNER JOIN artikel on trans2.artikel=artikel.artikel WHERE trans2.transtid between 'datum1' and 'datum2' (If recordcount 0) then SELECT SUM(trans2.nettovikt) INTO ut_summa FROM trans2 INNER JOIN artikel on trans2.artikel=artikel.artikel WHERE trans2.transtid between 'datum1' and 'datum2' and artikel.reservinteger='skatt' (If recordcount 0) then SELECT SUM(trans2.nettovikt) INTO ut_summa FROM trans2 INNER JOIN artikel on trans2.artikel=artikel.artikel WHERE trans2.transtid between 'datum1' and 'datum2' and artikel.reservinteger='skatt' group by artikel.volympris; Does this help??? Martin Gainty __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. Date: Fri, 23 Nov 2007 11:10:47 +0100 From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Problem with a Procedure Hi! Hope you can help me with this one. Im trying to learn this with stored procedures and optimize my databases. Can someone point what wrong with this? -- DELIMITER $$ DROP PROCEDURE IF EXISTS `vagsql`.`sok` $$ CREATE [EMAIL PROTECTED] PROCEDURE `sok`(skatt int, datum1 DATE, datum2 DATE, OUT ut_summa decimal(8,2)) BEGIN SELECT SUM(trans2.nettovikt) INTO ut_summa FROM trans2 INNER JOIN artikel on trans2.artikel=artikel.artikel WHERE trans2.transtid between 'datum1' and 'datum2' and artikel.reservinteger='skatt' group by artikel.volympris; END $$ DELIMITER ; call sok('01','2007-01-01 00:00:00', '2007-01-10 23:59:59', @out); select @out; All I get is that No data - zero rows fetched, selected or processed When running this in query brower everything looks ok. SELECT SUM(trans2.nettovikt) as summa FROM trans2 INNER JOIN artikel on trans2.artikel=artikel.artikel WHERE trans2.transtid between '2007-07-01 00:00:00' and '2007-07-02 23:59:59' and artikel.reservinteger='01' group by artikel.volympris; Im running mysql 5.1.11. Thanks in advance! /Tomas Share life as it happens with the new Windows Live. Share now! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]