At 06:15 AM 1/21/2010, Krishna Chandra Prajapati wrote:
Hi Abhishek.
insert, select and delete are in the same proportion.
1. Inserted data into a table A by user.
2. Selecting data from table A inserting data to table B after applying some
rules(update).
3. Deleting data from table A.
4. Selecting data from table B using some conditions (SELECT sql_id, momt,
sender, receiver, udhdata, msgdata, time, smsc_id, service, account, id,
sms_type, mclass, mwi, coding, compress FROM alt_send_sms WHERE smsc_id =
'ite' ORDER BY msg_priority, sql_id LIMIT 0,20) moving to third party for
sending sms.
5. Deleting the selected data from table B.
With the above scenario, i am not able to user concurrent connections. Other
wise it will send duplicate sms.
Thanks,
Kishna
Kishna,
If the table A and B are not doing Updates, then there is a solution
using MyISAM tables. :-)
1) Start by optimizing the tables to remove any deleted rows (holes) in the
table. Call this table "A". Do the same for Table "B".
2) Create a third table "DA" that contains one column, the Rcd_Id of the
rows in table A that needs deleting. You can create table "DB" with the
rcd_id of the deleted rows from table B. Of course tables DA and DB have an
index on this Rcd_Id column.
3) When you start the DA and DB tables are empty and optimized (no holes)
4) Instead of deleting rows from table A, you add its Rcd_id to table DA.
Same with table B and DB.
5) Your Select statements on table A and table B will do a Left Join to DA
and DB respectively as in:
select ... from A left join DA on A.Rcd_Id=DA.Rcd_Id where DA.Rcd_Id is NULL
or
select ... from B left join DB on B.Rcd_Id=DB.Rcd_Id where DB.Rcd_Id is NULL
Since DA.Rcd_Id and Db.Rcd_id are indexed, this will be quite fast. Make
them a memory table if you like.
Now what makes this work is MyISAM tables will NOT issue a lock when
inserting rows on an optimized table! This goes for both table A and DA.
Once a day you will delete the deleted rows from Table A using DA.
You may be able to reduce this time by taking advantage of the ability to
rename multiple tables at one time. See
http://dev.mysql.com/doc/refman/5.0/en/rename-table.html
so you ...
... can create a duplicate empty tables using:
create table DUPA like A; insert into DUPA select * from A left join DA on
A.Rcd_Id=DA.Rcd_Id where DA.Rcd_Id is null;
create table DUPDA like DA;
create table DUPB like B;insert into DUPB select * from A left join DA on
B.Rcd_Id=DB.Rcd_Id where DB.Rcd_Id is null;
create table DUPDB like DB;
drop table if exists DupA, DupDA, DupB, DupDB, OldA, OldB;
Now in one statement execute:
rename table A to OldA, DUPA to A, DA to OldDA, DupDA to DA, B to OldB,
DUPB to B, DB to OldDB, DupDB to DB;
This is atomic so all tables get renamed at once using one lock.
Now you have to copy the few rows that were inserted into A after DupA was
created and before the tables were renamed;
set @MaxRcdId := select max(Rcd_Id) from A;
Insert into A select * from OldA where Rcd_Id>@MaxRcdId;
Now you have to copy the few rows that were inserted into B after DupB was
created and before the tables were renamed;
set @MaxRcdId := select max(Rcd_Id) from B;
Insert into B select * from OldB where Rcd_Id>@MaxRcdId;
This theoretically should work. I've done this from the top of my head so
there may be syntax errors. This should get you on the right road.
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
