Scaling Mysql
Hi list, I have two tables send_sms and alt_send_sms. Users are inserting records into send_sms @ 500/sec ie 3/min. After applying some updates to send_sms data are transferred to alt_send_sms and deleted from send sms. The same thing is happening with alt_send_sms table. Is it possible to insert 1000records/sec in send_sms table and taken out at the rate 1000records/seconds from alt_send_sms. Which engine is more better for the above senario. Regards, Krishna
Write IF condition in Query , and mysql Search function
Hi All ,
Am working on the autosuggestion program
SELECT $field2,$field1 FROM $tblname where locate('$q', $field2) 0 OR
locate('$q', $field1) 0 order by locate('$q', $field2),
$field2 limit . ($pagesize * $page) . , . $pagesize
This is my query ,
Example in my Database table .. am having values somthing like
Bombay(BOB),Delhi(DI),Gujarath(GA),Rajasthan(RA),Baroda(BD)
My present act like ...
If user enter thed
then It show the baroda(BD) as first row and Delhi as second result
So can u please guide me here
Also for clear vision please go this link
http://ukatn.com/index_autosuggest.php
Select the Postal code in the Taxi From combo...
Then enter the l as keyword . then u will find the first are start with A
not an L ,
So my expectation is ,
Assume if user enter the L then i want the first result as start with L row
as out ... and if they enter LI then result must lilliput somthing like
that
Am fething columns are postcodename and postcodeCODE ..
IIn the search, the first preference must be field *postcodename *if no
keyword match in the first column then go second column *postcodeCODE *
.
Can u please tell me How to write query for this siutaion
Thanks
Re: alternate in mysql
Hi Johnny, Thanks for the reply, but the below sql does not seems to work SELECT * FROM tmp WHERE t REGEXP '^/sr/db/.*'; Empty set (0.02 sec) mysql SELECT * FROM tmp WHERE t REGEXP '/sr/db/.*'; ++ | t | ++ | asdf /sr/db/ora/ora.ora /sr/db/ora/aaa.ora asdlkjf | On Thu, Aug 20, 2009 at 8:19 PM, Johnny Withers joh...@pixelated.netwrote: You could try: SELECT * FROM tmp WHERE t REGEXP '^/sr/db/.*'; On Thu, Aug 20, 2009 at 9:15 AM, Ananda Kumar anan...@gmail.com wrote: Hi All, I have this data in both oracle and mysql. select * from tmp; T -- asdf /sr/db/ora/ora.ora asdfljk asdlkjf asdf /sr/db/ora/ora.ora /sr/db/ora/aaa.ora asdlkjf Where t is a varchar column, with each row having multiple lines. I can write this query in oracle to fetch only rows starting with ''sr/db select substr(t, instr(t, '/sr/db/'), instr(substr(t, instr(t, '/sr/db/'), length(t)), chr(10))) from tmp; /sr/db/ora/ora.ora /sr/db/ora/ora.ora where chr(10) ..represents NEW LINE in oracle How do i do the same in mysql. Thanks for all you help. regards anandkl -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: Scaling Mysql
Krishna Chandra Prajapati schrieb: Hi list, I have two tables send_sms and alt_send_sms. Users are inserting records into send_sms @ 500/sec ie 3/min. After applying some updates to send_sms data are transferred to alt_send_sms and deleted from send sms. The same thing is happening with alt_send_sms table. Is it possible to insert 1000records/sec in send_sms table and taken out at the rate 1000records/seconds from alt_send_sms. Which engine is more better for the above senario. Hi Krishna, i see you are using some kind of queue mechanism but to get a useful answer you need to be more specific: e.g. what are your safety requirements ? Tables in RAM are very fast. e.g. do you need forgein keys ? When will data be copied (send-alt) ? after 1 day ? 1 hour ? how long to you need to store data at alt ? how often is the access ? If speed is a concern do you need a database at all ? (KISS) where does the current system spend its time ? and why ? You see your request is far from simple and demands detail knowlegde about your requirements going beyound what can be done in such a ML (and this is only software, there is also hardware an economics). Here you can ask how can i improve SQL statement XX ? re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Scaling Mysql
At 01:30 AM 8/21/2009, Krishna Chandra Prajapati wrote: Hi list, I have two tables send_sms and alt_send_sms. Users are inserting records into send_sms @ 500/sec ie 3/min. After applying some updates to send_sms data are transferred to alt_send_sms and deleted from send sms. The same thing is happening with alt_send_sms table. Is it possible to insert 1000records/sec in send_sms table and taken out at the rate 1000records/seconds from alt_send_sms. Which engine is more better for the above senario. Regards, Krishna Krishna, Rather than copying rows from one table to another, and deleting the previous rows, why not just do: 1) create table send_sms_empty like send_sms; 2) rename table send_sms to send_sms_full;rename send_sms_empty to send_sms; 3) insert into alt_send_sms select * from send_sms_full; drop table send_sms_full; because step #2 is two sql statements, they will get executed together and will take just 1 or 2 ms and now you have an empty table that continues to get filled. This eliminates the insert delete table locking. Plus you always start with an empty optimized table. Step #3 uses a drop table which is much faster than trying to delete the rows. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Best practice to disable log bin for mysql_upgrade
I am upgrading minor MySQL versions, 5.0.67 to 5.0.84 I have a master-master replication setup, and want to upgrade both installations. The ideal procedure for upgrading mysql using the mysql_upgrade command is to have binary logging turned off during the execution of mysql_upgrade. My situation is I do not want to turn off binary logging for the entire server, I would like to turn off binary logging just for the session of the mysql_upgrade connection. mysql_upgrade does not support anything like --disable-log-bin (which seems is a feature that should be supported for this cli app) (1) So it seems my only option is to turn off binary logging for the entire server while I execute mysql_upgrade. Which also means blocking write access to the server while it runs so that statements I do want logged for replication do not occur while binary logging is off. Is there another simple way to achieve this? Or what is best practice that achieves the least amount of down time? Alternately, there are sql files in the share directory of each archive: mysql-5.0.67-linux-i686-glibc23/share/ |-- fill_help_tables.sql |-- mysql_fix_privilege_tables.sql |-- mysql_system_tables.sql |-- mysql_system_tables_data.sql |-- mysql_system_tables_fix.sql `-- mysql_test_data_timezone.sql mysql-5.0.84-linux-i686-glibc23/share/ |-- fill_help_tables.sql |-- mysql_fix_privilege_tables.sql |-- mysql_system_tables.sql |-- mysql_system_tables_data.sql |-- mysql_system_tables_fix.sql `-- mysql_test_data_timezone.sql Is it true (yes or no) that the difference between these 6 sql files from one distribution to the next is what would be changed if the mysql_upgrade was executed to upgrade an installation from 5.0.67 to 5.0.84 ? According to this: http://forge.mysql.com/worklog/task.php?id=4991 There are two scripts: share/mysql_system_tables.sql and share/mysql_system_tables_fix.sql These two scripts comprise the content of: share/mysql_fix_privilege_tables.sql The following is true about these three files: cat share/mysql_system_tables.sql share/mysql_system_tables_fix.sql share/mysql_fix_privilege_tables.sql To upgrade the schema of the mysql server, only the share/mysql_fix_privilege_tables.sql sql script need be applied. Is this correct? Is it true for MySQL 5.0.x ? References: (1) http://bugs.mysql.com/bug.php?id=46638thanks=4 mysql_upgrade needs --disable-log-bin option (2) http://forums.mysql.com/read.php?20,275140,275140#msg-275140 MySQL Forums :: General :: Best practice to disable log bin for mysql_upgrade (3) http://forums.mysql.com/read.php?11,274796,274796#msg-274796 MySQL Forums :: Install :: How to best disable log bin for mysql_upgrade -RG -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: alternate in mysql
-Original Message- From: Ananda Kumar [mailto:anan...@gmail.com] Sent: Friday, August 21, 2009 2:55 AM To: Johnny Withers Cc: MySQL Subject: Re: alternate in mysql Hi Johnny, Thanks for the reply, but the below sql does not seems to work SELECT * FROM tmp WHERE t REGEXP '^/sr/db/.*'; Empty set (0.02 sec) mysql SELECT * FROM tmp WHERE t REGEXP '/sr/db/.*'; ++ | t | ++ | asdf /sr/db/ora/ora.ora /sr/db/ora/aaa.ora asdlkjf | On Thu, Aug 20, 2009 at 8:19 PM, Johnny Withers joh...@pixelated.netwrote: You could try: SELECT * FROM tmp WHERE t REGEXP '^/sr/db/.*'; On Thu, Aug 20, 2009 at 9:15 AM, Ananda Kumar anan...@gmail.com wrote: Hi All, I have this data in both oracle and mysql. select * from tmp; T -- asdf /sr/db/ora/ora.ora asdfljk asdlkjf asdf /sr/db/ora/ora.ora /sr/db/ora/aaa.ora asdlkjf Where t is a varchar column, with each row having multiple lines. I can write this query in oracle to fetch only rows starting with ''sr/db [JS] I might not understand what you want to do, especially since you begin your string with two single-quotes and end it with one double-quote. You want to find those ROWS (not lines) in which `t` begins with the five characters s, r, /, d, b? Would SELECT `t` FROM `tmp` WHERE `t` LIKE 'sr/db%'; do what you want? If you want to find those LINES which begin with the five characters, then you have a much bigger headache. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com select substr(t, instr(t, '/sr/db/'), instr(substr(t, instr(t, '/sr/db/'), length(t)), chr(10))) from tmp; /sr/db/ora/ora.ora /sr/db/ora/ora.ora where chr(10) ..represents NEW LINE in oracle How do i do the same in mysql. Thanks for all you help. regards anandkl -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Scaling Mysql
Krishna, Rather than copying rows from one table to another, and deleting the previous rows, why not just do: 1) create table send_sms_empty like send_sms; 2) rename table send_sms to send_sms_full;rename send_sms_empty to send_sms; 3) insert into alt_send_sms select * from send_sms_full; drop table send_sms_full; because step #2 is two sql statements, they will get executed together and will take just 1 or 2 ms and now you have an empty table that continues to get filled. This eliminates the insert delete table locking. Plus you always start with an empty optimized table. Step #3 uses a drop table which is much faster than trying to delete the rows. [JS] You'd have to make sure that the application, which is after all pounding the database pretty hard, doesn't gag. As fast as that operation might be, the application is likely to collide with it. You cannot rename a locked table, so I'm not sure how you could do what you are suggesting in an ACID way. You'd need some kind of semaphore somewhere. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the- infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: alternate in mysql
Yeah.. I think i misunderstood what he is trying to do here.. I thought he only wanted rows where t BEGINS with /sr/db, but I think he wants rows that have a line in column t that begins with that -- then of those rows, he only wants t to contain the lines within t that begin with /sr/db as well. Maybe? On Fri, Aug 21, 2009 at 10:13 AM, Jerry Schwartz jschwa...@the-infoshop.com wrote: -Original Message- From: Ananda Kumar [mailto:anan...@gmail.com] Sent: Friday, August 21, 2009 2:55 AM To: Johnny Withers Cc: MySQL Subject: Re: alternate in mysql Hi Johnny, Thanks for the reply, but the below sql does not seems to work SELECT * FROM tmp WHERE t REGEXP '^/sr/db/.*'; Empty set (0.02 sec) mysql SELECT * FROM tmp WHERE t REGEXP '/sr/db/.*'; ++ | t | ++ | asdf /sr/db/ora/ora.ora /sr/db/ora/aaa.ora asdlkjf | On Thu, Aug 20, 2009 at 8:19 PM, Johnny Withers joh...@pixelated.netwrote: You could try: SELECT * FROM tmp WHERE t REGEXP '^/sr/db/.*'; On Thu, Aug 20, 2009 at 9:15 AM, Ananda Kumar anan...@gmail.com wrote: Hi All, I have this data in both oracle and mysql. select * from tmp; T -- asdf /sr/db/ora/ora.ora asdfljk asdlkjf asdf /sr/db/ora/ora.ora /sr/db/ora/aaa.ora asdlkjf Where t is a varchar column, with each row having multiple lines. I can write this query in oracle to fetch only rows starting with ''sr/db [JS] I might not understand what you want to do, especially since you begin your string with two single-quotes and end it with one double-quote. You want to find those ROWS (not lines) in which `t` begins with the five characters s, r, /, d, b? Would SELECT `t` FROM `tmp` WHERE `t` LIKE 'sr/db%'; do what you want? If you want to find those LINES which begin with the five characters, then you have a much bigger headache. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com select substr(t, instr(t, '/sr/db/'), instr(substr(t, instr(t, '/sr/db/'), length(t)), chr(10))) from tmp; /sr/db/ora/ora.ora /sr/db/ora/ora.ora where chr(10) ..represents NEW LINE in oracle How do i do the same in mysql. Thanks for all you help. regards anandkl -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net
RE: Scaling Mysql
RENAME statement is atomic, and you can specify multiple tables to rename at once. Instead of two statements, do this: rename table send_sms to send_sms_full, send_sms_empty to send_sms; There will be no gap in-between. -Original Message- From: Jerry Schwartz [mailto:jschwa...@the-infoshop.com] Sent: Friday, August 21, 2009 8:32 AM To: 'mos'; 'MySQL' Subject: RE: Scaling Mysql Krishna, Rather than copying rows from one table to another, and deleting the previous rows, why not just do: 1) create table send_sms_empty like send_sms; 2) rename table send_sms to send_sms_full;rename send_sms_empty to send_sms; 3) insert into alt_send_sms select * from send_sms_full; drop table send_sms_full; because step #2 is two sql statements, they will get executed together and will take just 1 or 2 ms and now you have an empty table that continues to get filled. This eliminates the insert delete table locking. Plus you always start with an empty optimized table. Step #3 uses a drop table which is much faster than trying to delete the rows. [JS] You'd have to make sure that the application, which is after all pounding the database pretty hard, doesn't gag. As fast as that operation might be, the application is likely to collide with it. You cannot rename a locked table, so I'm not sure how you could do what you are suggesting in an ACID way. You'd need some kind of semaphore somewhere. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the- infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Scaling Mysql
-Original Message- From: Gavin Towey [mailto:gto...@ffn.com] Sent: Friday, August 21, 2009 2:45 PM To: Jerry Schwartz; 'mos'; 'MySQL' Subject: RE: Scaling Mysql RENAME statement is atomic, and you can specify multiple tables to rename at once. Instead of two statements, do this: rename table send_sms to send_sms_full, send_sms_empty to send_sms; [JS] Ah, I didn't think about that. There will be no gap in-between. -Original Message- From: Jerry Schwartz [mailto:jschwa...@the-infoshop.com] Sent: Friday, August 21, 2009 8:32 AM To: 'mos'; 'MySQL' Subject: RE: Scaling Mysql Krishna, Rather than copying rows from one table to another, and deleting the previous rows, why not just do: 1) create table send_sms_empty like send_sms; 2) rename table send_sms to send_sms_full;rename send_sms_empty to send_sms; 3) insert into alt_send_sms select * from send_sms_full; drop table send_sms_full; because step #2 is two sql statements, they will get executed together and will take just 1 or 2 ms and now you have an empty table that continues to get filled. This eliminates the insert delete table locking. Plus you always start with an empty optimized table. Step #3 uses a drop table which is much faster than trying to delete the rows. [JS] You'd have to make sure that the application, which is after all pounding the database pretty hard, doesn't gag. As fast as that operation might be, the application is likely to collide with it. You cannot rename a locked table, so I'm not sure how you could do what you are suggesting in an ACID way. You'd need some kind of semaphore somewhere. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the- infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Scaling Mysql
At 01:44 PM 8/21/2009, you wrote: RENAME statement is atomic, and you can specify multiple tables to rename at once. Instead of two statements, do this: rename table send_sms to send_sms_full, send_sms_empty to send_sms; There will be no gap in-between. Ah yes, that's what I forgot. It's all done in one sql statement. Mike -Original Message- From: Jerry Schwartz [mailto:jschwa...@the-infoshop.com] Sent: Friday, August 21, 2009 8:32 AM To: 'mos'; 'MySQL' Subject: RE: Scaling Mysql Krishna, Rather than copying rows from one table to another, and deleting the previous rows, why not just do: 1) create table send_sms_empty like send_sms; 2) rename table send_sms to send_sms_full;rename send_sms_empty to send_sms; 3) insert into alt_send_sms select * from send_sms_full; drop table send_sms_full; because step #2 is two sql statements, they will get executed together and will take just 1 or 2 ms and now you have an empty table that continues to get filled. This eliminates the insert delete table locking. Plus you always start with an empty optimized table. Step #3 uses a drop table which is much faster than trying to delete the rows. [JS] You'd have to make sure that the application, which is after all pounding the database pretty hard, doesn't gag. As fast as that operation might be, the application is likely to collide with it. You cannot rename a locked table, so I'm not sure how you could do what you are suggesting in an ACID way. You'd need some kind of semaphore somewhere. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the- infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Select clause using from and to (like rownum in Oracle)
Hi All, I am facing a problem in porting an application written for oracle to run on mysql. The application uses a sqlmap (ibatis) at the heart of which is basically a file that defines all sql's used in the application. It is very well organized this way. The application uses Oracle as the database. The problem is that for pagination purposes the sql's written use rownum and accept 2 arguments - the from rownum and the to rownum. I am trying to run the same application on my laptop that runs mysql. I have migrated all data and all the sql queries work perfectly except the one that use pagination and the rownum. I know in mysql there is support for sql using the LIMIT clause, but the LIMIT seems to take 2 arguments, the first one being the start rownum and the second being the number of rows to output. I need the second to be the to rownum. I have done a lot of googling, but apart from just putting a rownum for the sql output there was no real usages for pagination purposes. I cannot use the LIMIT as it is in mysql, because that would mean I would have to change the application logic which I do not want to do. I also do not want to install Oracle on my laptop, just too heavy. I have found this to work except I am not sure how to pass a where clause for the rownum part: SELECT @rownum:=...@rownum+1 rownum, t.*FROM (SELECT @rownum:=0) r, user_approvers t I was trying something like: SELECT @rownum:=...@rownum+1 rownum, t.*FROM (SELECT @rownum:=0) r, user_approvers t where r.rownum between 10, 20; or even SELECT @rownum:=...@rownum+1 rownum, t.*FROM (SELECT @rownum:=0) r, user_approvers t where r.rownum=1; I get the error: ERROR 1054 (42S22): Unknown column 'r.rownum' in 'where clause' Is there anyway the SELECT query can be forced to use the from and to rownum parameters? Thanks a lot for any help, Anoop
Re: Select clause using from and to (like rownum in Oracle)
Is there anyway the SELECT query can be forced to use the from and to rownum parameters? 1st LIMIT arg = OracleFromArg 2nd LIMIT arg = OracleToArg - OracleFromArg + 1 so 'from 11 to 20' becomes LIMIT 11,10. PB - Anoop kumar V wrote: Hi All, I am facing a problem in porting an application written for oracle to run on mysql. The application uses a sqlmap (ibatis) at the heart of which is basically a file that defines all sql's used in the application. It is very well organized this way. The application uses Oracle as the database. The problem is that for pagination purposes the sql's written use rownum and accept 2 arguments - the from rownum and the to rownum. I am trying to run the same application on my laptop that runs mysql. I have migrated all data and all the sql queries work perfectly except the one that use pagination and the rownum. I know in mysql there is support for sql using the LIMIT clause, but the LIMIT seems to take 2 arguments, the first one being the start rownum and the second being the number of rows to output. I need the second to be the to rownum. I have done a lot of googling, but apart from just putting a rownum for the sql output there was no real usages for pagination purposes. I cannot use the LIMIT as it is in mysql, because that would mean I would have to change the application logic which I do not want to do. I also do not want to install Oracle on my laptop, just too heavy. I have found this to work except I am not sure how to pass a where clause for the rownum part: SELECT @rownum:=...@rownum+1 rownum, t.*FROM (SELECT @rownum:=0) r, user_approvers t I was trying something like: SELECT @rownum:=...@rownum+1 rownum, t.*FROM (SELECT @rownum:=0) r, user_approvers t where r.rownum between 10, 20; or even SELECT @rownum:=...@rownum+1 rownum, t.*FROM (SELECT @rownum:=0) r, user_approvers t where r.rownum=1; I get the error: ERROR 1054 (42S22): Unknown column 'r.rownum' in 'where clause' Is there anyway the SELECT query can be forced to use the from and to rownum parameters? Thanks a lot for any help, Anoop No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.409 / Virus Database: 270.13.63/2317 - Release Date: 08/21/09 06:04:00
Select clause using from and to (like rownum in Oracle)
Never mind. I got it to work.. I had to really trim down the entire statement: set @sql = concat( select iams_id as iamsId ,division_name as divisionName ,region_name as regionName ,isactive as isActive from user_approvers limit , #from#, ,, (#from#-#to#+1) ); prepare stmt from @sql; execute stmt; drop prepare stmt; But I am not able to use it as a sqlmapped statement in iBatis, but that is a separate problem for a different user list.. but you gave me the idea so far and it works. Thanks very much. Thanks, Anoop On Fri, Aug 21, 2009 at 8:26 PM, Anoop kumar V anoopkum...@gmail.comwrote: I am having trouble executing what you have sent. Below is output mysql set @sql = concat( select iams_id as iamsId ,division_name as divisionName ,region_name as regionName ,isactive as isActive from ( select iams_id ,division_name ,region_name ,isactive from user_approvers ) order by rn limit , 10, ,, (20-10+1) ); Query OK, 0 rows affected (0.03 sec) mysql prepare stmt from @sql; ERROR 1248 (42000): Every derived table must have its own alias mysql execute stmt; ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to EXECUTE mysql drop prepare stmt; ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE mysql mysql set @sql = concat( select iams_id as iamsId ,division_name as divisionName ,region_name as regionName ,isactive as isActive from ( select iams_id ,division_name ,region_name ,isactive from user_approvers ) a order by rn limit , 10, ,, (20-10+1) ); Query OK, 0 rows affected (0.00 sec) mysql prepare stmt from @sql; ERROR 1054 (42S22): Unknown column 'rn' in 'order clause' mysql execute stmt; ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to EXECUTE mysql drop prepare stmt; ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE mysql mysql set @sql = concat( select iams_id as iamsId ,division_name as divisionName ,region_name as regionName ,isactive as isActive from ( select iams_id ,division_name ,region_name ,isactive from user_approvers ) a limit , 10, ,, (20-10+1) ); Query OK, 0 rows affected (0.00 sec) mysql prepare stmt from @sql; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'limit 10,11' at line 13 mysql execute stmt; ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to EXECUTE mysql drop prepare stmt; ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE mysql mysql Thanks, Anoop On Fri, Aug 21, 2009 at 7:22 PM, Peter Brawley peter.braw...@earthlink.net wrote: I think you'd need to use Prepare, eg replace the query with ... set @sql = concat( select user_id as iamsId ,division_name as divisionName ,region_name as regionName ,isactive as isActive from ( select user_id ,division_name ,region_name ,isactive from user_approvers ) order by rn limit , #from, ,, (#to-#from+1) ); prepare stmt from @sql; execute stmt; drop prepare stmt; PB - Anoop kumar V wrote: Thanks very much Peter. But I think I did figure that much. What I am lacking is the integration of that logic into the sql. The current sql (made for oracle) is like this - I can change it all I want because of the sql map which is configurable... select user_id as iamsId ,division_name as divisionName ,region_name as regionName ,isactive as isActive from ( select user_id ,division_name ,region_name ,isactive ,row_number() over (order by division_name, region_name) rn from user_approvers ) where rn between #from# and #to# order by rn I can change everything but the parameters to the sql: #from# and #to#. These come from the application logic and is user enterred (not directly, but through pagination etc - you get the idea) I tried things like the following (to get rows from 11 to 20): select * from user_approvers limit 10, 20-10; Also tried assigning variables.. still no go.
Re: Scaling Mysql
Hi wharms, Yor are right. It's some kind of queue mechanism. Right now i am working i telco company (We used to send sms) Users will be inserting records into send_sms @ 30,000msg/min Then those record will be updated and moved to alt_send_sms and deleted from send_sms. After that 30,000msg/min will be taken out from alt_send for processing and sending to client. All the above task are happening concurrently. We will be dealing with million of records/hour On Fri, Aug 21, 2009 at 6:11 PM, walter harms wha...@bfs.de wrote: Krishna Chandra Prajapati schrieb: Hi list, I have two tables send_sms and alt_send_sms. Users are inserting records into send_sms @ 500/sec ie 3/min. After applying some updates to send_sms data are transferred to alt_send_sms and deleted from send sms. The same thing is happening with alt_send_sms table. Is it possible to insert 1000records/sec in send_sms table and taken out at the rate 1000records/seconds from alt_send_sms. Which engine is more better for the above senario. Hi Krishna, i see you are using some kind of queue mechanism but to get a useful answer you need to be more specific: e.g. what are your safety requirements ? After moving the data to next stage Data is deleted from current table. Tables in RAM are very fast. e.g. do you need forgein keys ? No When will data be copied (send-alt) ? 3records/min after 1 day ? 1 hour ? how long to you need to store data at alt ? Min 1 minute (we need to process immeaditely and send to the users as sms) how often is the access ? per/sec If speed is a concern do you need a database at all ? (KISS) where does the current system spend its time ? and why ? You see your request is far from simple and demands detail knowlegde about your requirements going beyound what can be done in such a ML (and this is only software, there is also hardware an economics). Here you can ask how can i improve SQL statement XX ? re, wh Thanks krishna
