MYSQL :: Store Inserts/deletes

Multiple Inserts And Deletes
I'm using PHP/mySQL couple :) to insert and delete some informations
based on some criterium and I don't like to look at my code full of
FOR and IF's.

1)
Example: In a list of ID's 1 4 5 7 8 9 20 34 56 is possible to send an
UNIQUE command to mysql to delete the Id's 5, 7, 20, etc? Need I put a
ugly query like "Delete ... WHERE id=5 AND id=7 AND id=20" ad
infinitum?

2) How can I check without loops if a given value exist in the table?
Example: id's 1 3 5 7 8 must be inserted ONLY IF 3 or 5 doesn't exist.

Finally, It's very hard to do some search for examples to know how to
use the keyword "IN" , any hint please?

Clean Up Tables After Deletes
I noticed that in phpMyAdmin there is a way to cleanup and compact
tables that have had lots of deletes done to them. Can anyone tell me
how to do that manually? I find phpMyAdmin to be a pain so I don't want
to use it but I would like to compact some tables that got bloated with
testing and are now much smaller than before. The main reason I want to
clean them up is that new records go into slots previously used and
deleted instead of going at the end, where they naturally belong
(chronologically).

Large Tables, Very Slow Deletes.
I've been using MySQL for a while now and are starting to run into limitations. Either my own, or something else.

I have this really large table, it stores images, it has a mediumblob field, an unsigned id integer field and a timestamp field. This table contains up to a few million rows and is constantly filled real time. It grows up to sizes between 50 and 100 gb and in the future probably even larger.

This data is not supposed to be stored for all time, therefor when a certain treshhold is reached, the oldest 5000 rows are deleted to make room for newer rows. This is where my problem kicks in. Whenever I try to delete those rows, it can take for ever to complete. 500 rows I can delete without problem, 2500 becomes slow, 5000 rows takes several seconds and 10.000 rows or more makes it looks like things are frozen.

Now the temporary solution I have is to delete 500 rows at time, but this means I must do that every 10 seconds just to maintain status quo. I would much rather check every 5 minutes or so, and if needed delete 15.000 rows in one go.

I use MyISAM tables and tried setting the key size to 128M, no luck. I run this on a dual Opteron system with a GB of memory and WinXP-SP2 Proffesional.

Mysql_affected_rows And Recursive Cascading Deletes Possible
I have 5.1.17 and one table 'connected' to itself via a foreign key (see below)
When I delete a parent post with children they all get deleted BUT a subsequent call to mysql_affected_rows always returns 1.

I would expect/hope that it returns the total number of deleted posts(including any cascading ones) Am I wrong??

Audit Trail Of Updates And Deletes
In my application I need to keep track of all the changes, especially
update,

and delete changes each record. I need to keep track of who did
what changes. Is there any audit trail feature in MySQL 4.1? Or is
there a simple data schema, which can accomplish that.

Questionable Row Inserts
with mysql table colum set at varchar(15), somebody recently managed to do an insert having 16 characters. Too, the regular expression on the insert form for validations is at 15

through a textarea input area on the form, this is the start of the inserted values, followed by a very long list of hyperlinks (mainly gambling sites, etc):

"Vnpqxs
Of Action "

- how is the greater than varchar(15) possible?
- does this look like someone could be trying to run scripts from within the table?
- can I assume my insert form was bypassed?

an ip lookup revealed Amsterdam (my site is a Los Angeles based city site)

Multiple INSERTS At Once Possible?
say i have table users that holds names, and i have a page that has 10 textfields through which i can import new names.

Is it possible to enter all 10 at once rather than:

$query = mysql_query("INSERT INTO users VALUES '".$_POST['t1']."');
$query = mysql_query("INSERT INTO users VALUES '".$_POST['t2']."');
$query = mysql_query("INSERT INTO users VALUES '".$_POST['t3']."');
$query = mysql_query("INSERT INTO users VALUES '".$_POST['t4']."');

Auto_number And Inserts
When I do an insert I am doing an insert and an update immediately afterwards - can this be modified to a straight insert for performance enhancement at all?

Stored procedure section:

insert into tree_node(name, parent) values ('x', 123);
select max(id) into _created_id from tree_node;
update tree_node set self = _created_id;

I'd like to do something like:
insert into tree_node(name, parent, self) values ('x', 123, NEW_ID);

is that possible?

MULTIPLE INSERTS
Is it possible to simultaneously insert a row into different tables using different column names in each table and different/similar values.

EG:

INSERT INTO table1 (col1, col2, col3) values (val1,val2,val3)

INSERT INTO table2 (col5, col6, col7 ,col8) value(val1,val2,val6,val8)

(BOTH above inserts must be carried out at the same time)

This would process both inserts, and if one insert failed then the other would fail also? Similar to transactions in MS SQL Server.

Multiple Inserts
I'm saving data into a table that has a list format. So I usually have 5-10 rows of data each with about 4-5 columns. Is it more efficient to insert once with multiple rows like

INSERT into TABLE VALUES (val1, val2, val3), (val4,val5,val6) etc

or should i just insert once for each row?
Are there performance/security pros and cons to either approach?

PhpMyAdmin - Use Ignore Inserts
What does the "Use ignore inserts" option within phpMyAdmin in the "export" tab do?

Am I right in thinking it will ignore an insert when the primary key already exists?

What is the best way to do inserts in such a way that incase a record with the primary key already exists, it will simply overwrite it in the database?

I Have Way Too Many Inserts/updates In My Forum
when someone posts a thread theres 2 count queries
2 fetch queries
3 insert queries
and 2 update queries

so my question is, is there a way to do a sort of join query for inserts and updates?

also, has anyone got some mysql musts?
i dont see a mysql tips thread
i think there should be one

Inserts - MyISAM Vs InnoDB
I am making a logging script which will log every request to the site. The frequency of inserts will be approximately 60K per hour. After every 1 hour a cronjob will generate a report of the data, save it to in a different table and truncate this logging table. So this table will have only 1 hour data.

I am logging IP, user-agent and page visited. I have created

Today I have used both table types one by one to experiment. I used INSERT DELAYED with MyISAM and INSERT with InnoDB. But when I viewed the processlist, there were a lot of threads in sleep state (in both cases). Why it was so?

Question: Which table type is best suitable for this need?

I am using Linux, PHP 4.4 and MySQL 4.1.

Trouble With Indexes And Inserts
Here's the situation(mysql 5.1)
(all the sql commands are below for easy replication)

If I put an index of length shorter than the field it is based on, I can't insert a row into a new table if that row has a value of that field longer than the index but shorter than the field definition. This is probably not a completely accurate description, but I'll show what I mean by a simple sequence.

1.Create two tables. table 1 is the source. table2 will be getting the inserts. table1 has an index (ix_org) which is 10 long, while org, the field it is based on is 30 long.

create table table1 (
name varchar(30),
place smallint,
org varchar(30),
index ix_org (org(10))
) engine = MyISAM;

create table table2 (
name varchar(30),
place smallint,
org varchar(30),
place_total varchar(10)
) engine = MyISAM;

2. Insert some data into table1. Two rows: one row with a "org" field length shorter than 10 and another with a field length longer than 10.

insert into table1 (name, place, org)
values ('Joe',Ƌ',ƍ_chars'),
('Fred',ཆ',ཉ_characters');

3. now do the inserts

test 1:

insert into table2 select
name as name,
place as place,
org as org,
concat(place,'/', (select max(place) from table1 where org=ƍ_chars')) as place_total
from table1 t1;

Result: all is good. in this case we are joining (in the subquery) on a value of "org" which is less than 10 characters > two rows inserted.

test 2:

(now join in the subquery with field length of org longer than 13)

insert into table2 select
name as name,
place as place,
org as org,
concat(place,'/', (select max(place) from table1 where org=ཉ_characters')) as place_total
from table1 t1;

Result: does not insert. error "data too long for column org at row1".

Other tests:
* if the length of the index is made the same length as the field, the error doesn't occur.
* if the subquery is not in the insert command, the error doesn't occur.

Duplicate Inserts For Orders
Currently using php/mysql for an order system and now that it's getting alot more visitors I'm having a problem with multiple orders getting inserted into the database.

I think I'll have to convert the table to InnoDB but wanted to check incase there was another trick I was missing.

The short of it is this php .....

Dumping Specific Inserts
I need to do a mysqldump of specific inserts, updates & deletes. I read in the docs that I can use -w or --where. Can I do a join?

Mysqldump And Extended-inserts
With --extended-insert=TRUE on by default, the line length for a tables records can be very long in the dump file. Is there a limit to this length, or a limiting buffer size perhaps that comes into play when restoring the data using the 'source' command in the 'mysql' command line utility?

Default Inserts Script
I am transcribing my default inserts from MSSQL_Inserts.sql to MYSQL_Inserts.sql. However, the problem I am running into is the use of variables, can anyone help? As an example:

MSSQL_Inserts.sql

use database;

DECLARE @variable varchar(16);
SET @variable = â€˜Some Valueâ€™;

INSERT INTO table VALUE(@variable);

Of course this is a snippet of what I actually have.

What would be the equiv. of MYSQL_Inserts.sql?

Inserts With Auto Increase
I have a problem with inserting into more then one table.

Ive got 3 Tables:

T1:
id (primery key with autoincrease )
Data...int

T2:
id (primery key with autoincrease )
OtherData...int

T3:
id (primery key with autoincrease )
T1_id...is connceted by a key with the id of T1
T2_id...is connceted by a key with the id of T2
some other Data

Now I want to write a UpdateScript that inserts:
One Row into T1, One Row into T2 and finaly:
One Row with the new ids from T1 and T2 into T3

Escaping Before Inserts - Get_magic_quotes_gpc
This questions is directed towards PHP/MySQL folk and relates to escaping hooks, apostraphe's and other characters that can create a security hole when writing to databases/files. I've been reading http://ca2.php.net/manual/en/functi...-quotes-gpc.php and just need to confirm a couple of things:

If I have magic_quotes_gpc on, and I use addslashes() - Does this in effect cause me to take security one step forward, and then back again? I mean, if magic_quotes_gpc is on, it will escape all my data before writing it to the database - But if I also use addslashes() will it not escape the escapes put in by magic_quotes_gpc?

When I perform a SELECT at the moment, the data that contains special characters is being returned with a backslash... This is wrong, correct? Because a properly escaped character should be stored without the backslash, true? Thus this means my quotes, or double quotes should be stored in my table, and the quotes should not be preceeded by the backslash character as part of the returned string from my SELECT.

How can I test that I am storing my data properly? (Thus, how can I perform a friendly attack on my database through my client HTML forms). I've tried `/bin/ls -l > /tmp/rd1` but this does not create a temp file in my temp directory - Thus, does this mean I have myself secure against this sort of common hack attack?

High Volume Of Inserts
I'm working on a pretty simple log analyzing stat app, and I've come to a point where I will need to make a large volume (2000+) of inserts into a mysql database.

Is there a way I can do this without crashing my server?

INSERT Inserts Wrong Value
As the title says, an incorrect value is being stored in my table when doing an SQL query.

INSERT INTO picks (id,player_id,position) VALUES(3,20050919104352853297,1)

None of those is the primary key, yet the middle number gets messed up when stored. The current field type is varchar(35), latin1_swedish_ci.

The value that's being inserted is:

20050919104352854016, so just the last 4 numbers are changing.

Innodb Concurrent Inserts
How can I write some deadlock-free MySQL code that says:

"If a certain record doesn't exist yet, then create it."

I tried using "LOCK TABLES", but it's throwing deadlocks because that same table is used in a transaction and which has an IS and IX lock while my "LOCK TABLES" is active.
It's particularly important that concurrent threads that are running this code do not end up inserting duplicate records.

Dumping Specific Inserts
I need to do a mysqldump of specific inserts, updates & deletes. I
read in the docs that I can use -w or --where.
Can I do a join?
Could someone provide an example?
Is there another way?

Extremely Slow INSERTs
Im inserting about 4,500 records into a table. There are only three fields each containing text.

Im using PHP to insert these records, but it's REALLY slow (can take like 5 minutes to insert line by line. I also tried generating a SQL file and inserting like that, but it still takes a couple of minutes.Is there anythign I should be checking in the mySQL config or something to make this faster??

Inserts Null Values
I'm the administrator of a project that will be using mysql and php to drive a web site. I've gotten a set of tables from the developer, created the database, and run the script he sent to create the tables. But now when I try to import the data tables
(shell>mysqlimport -u mysql -p dbname datatable.sql) all that goes into the database table are null values.
There are the correct number of columns and the row id is correct, but none of the data in the .sql files is acutally making it into the database.
I'm pretty new at this and am hoping to sell my boss on the mySQL/php solution so any help would be appreciated.

Performance Tuning For Inserts
i am inserting, usually, about 1-5 million records into a 50-350 million (depends on
which DB i'm using) DB. The table has about 75 columns in it. I am getting, on average,
processing time of about 6 seconds per thousand which is unacceptable. I need to
speed this process up. Right now i am building a string in Java with "insert into
<tableName> values(.......);" but I'm sure there's gotta be a faster way. Would it
be faster to write all the additions to a seperate file and then load it using 'LOAD
DATA LOCAL INFILE...'?

Temporary Table Inserts?
I was wondering, we all know that temporary tables are system bottleneck, I was wondering if the slowness of inserts for temporary table is related to the number of fields that the temporary table have? What I mean is, would a temporary table with 2 fields run INSERT statements faster than a temporary table with 5 fields?

Non-redundant Row Inserts And Foreign Keys
create table A (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
data int(10),
PRIMARY KEY (id)
)

create table B (
Bid int(10) unsigned NOT NULL,
Aid int(10) unsigned NOT NULL
)

where A stores expensive to compute and store items, and B defines sets of these items.

I'm creating sets of the data items dynamically, so something like:

insert into A values (NULL,1)
insert into B values (1,LAST_INSERT_ID())
insert into A values (NULL,2)
insert into B values (1,LAST_INSERT_ID())
insert into A values (NULL,3)
insert into B values (1,LAST_INSERT_ID())

and then, at some later time,

insert into A values (NULL,1)
insert into B values (2,LAST_INSERT_ID())
insert into A values (NULL,3)
insert into B values (2,LAST_INSERT_ID())
insert into A values (NULL,5)
insert into B values (2,LAST_INSERT_ID())

...representing the sets {1,2,3} and {1,3,5}.

Unfortunately, A now has 6 rows:

A:
1, 1
2, 2
3, 3
4, 1
5, 3
6, 5

I want some way to manage A so that I don't have to do multiple statements to discover whether data is already present or not, and to get its id, so that the tables look like this:

A:
1, 1
2, 2
3, 3
4, 5

B:
1, 1
1, 2
1, 3
2, 1
2, 3
2, 4

Load Infile Only Inserts One Record
I'm trying to import some data that I've saved as CSV files. Whenever I attempt to import them using "load infile" I only get a single record inserted into the database. I'm working in Mac OSX 10.3.9, and have the latest version of MySQL as of March 15. I'm pretty sure that the CSV files are all terminated with a single carriage return (
), though I've tried setting the load infile to use a carriage return/linefeed combination (), too. Code:

Best Way To Issue Hundreds Of Inserts/updates?
What is the best way to execute several (hundreds of) inserts and updates?
Rather than issuing tons of individual inserts and updates, can I send the
strings to a text file and then have mysql do them all??

IE :
query.txt

insert into table1 (col1,col2) values (1,2);
insert into table1 (col1,col2) values (9,4);
update table1 set col2=0 where col1=9;
insert into table1 (col1,col2) values (5,2);
insert into table1 (col1,col2) values (6,2);
insert into table1 (col1,col2) values (4,2);

Whats the best/fastest way to execute a few hundred of these?
Should I lock the table and then somehow use mysql.exe to run all the
commands in the txt file??

Inserts That Auto-record A Timestamp ??
I have several tables which I would like to record when a record has
been inserted or updated (say, for example, using the unix timestamp).

Is there a method whereby on every insert or update, that I can have a
column automatically assigned a date value?

My guess is no - but I thought I'd ask before I re-invent the wheel by
re-editing my code...

Prepared Statements (for Batch Inserts)
I am looking into using prepared statements as a possible performance optimisation in my application. I am currently using a batch insert statement:

insert into mytable values (d11, d12, d13), (d21, d22, d23),...,(dn1, dn2, dn3)

The database insert/select logic is multi-threaded. The number of rows that are to be inserted in the batch is variable. I am using Connector/NET to access MySQL from the application.

MySqlCommand as far as I can tell is not thread safe which means creating a new command instance for each statement the application needs to execute for each thread.

How does this affect prepared statements ? Are they automatically cached under the hood so if a new MySqlCommand is created using a previosuly prepared statement, is this handled so as to prevent "preparing" the statement again ? Do I need to cache each MySqlCommand instance for each unique prepared statement ?

What performance gains if any will I get if I use prepared statements instead of non-prepared statements for batch inserts as shown above?

How would I define the prepared statement parameters for a batch insert with a variable number of rows ?

MySQL Inserts Issuing Warnings
Can anyone tell me if MySQL >= v. 4.1.1 issues warnings for single row inserts? I know in previous versions it would only issue warnings during inserts if it was a multiple row insert, but the manual states:

Quote (MySQL Manual):

In 4.1.1, the situation is much improved, with warnings generated for statements such as LOAD DATA INFILE and DML statements such as INSERT, UPDATE, CREATE TABLE, and ALTER TABLE.

Table Inserts Text That Can Contain " And '
I've got a table insert that looks like:

INSERT INTO TABLENAME (iSbn,year,title) VALUES(111111,2006,"Title Name");

So far so good - the first 2 values are numeric, the 3rd textual (VARCHAR).

But some of the text items can contain either " or ' or both.
EG1. Brazil: The "Love Conquers All" Version
EG2. Something's Gotta Give

How do I fomat the insert command so that the " and ' chars get added as they should?

Want To Speed Up Mass INSERTS When There Is Processing Of The Insert_id()...
I am importing data from a large plaintext data set and it is going very slowly. I need to acquire and process the insert_id from each record so I cannot use INSERT DELAYED or LOAD DATA INFILE, for which I could speed things up by increasing the bulk_insert_buffer size. I'm not sure if there is really anything that can be done, but I thought I'd ask.

The looping process is like this.

1. insert row into table 1
2. get insert id
3. one or more inserts on table 2 using the insert_id

Repeat 2000 times or so per page load, because PHP just dies if the file isn't processed in small result sets. It is partly slow because table 1 has several indexes and table 2 has one. The indexes cannot just be added at the end either. In the end there will be +30million rows in table 1 and probably more than 100million in table 2 so if I tried to add the indexes at the end php would die off long before the script could complete.

Left Join Inserts Unknown Character
On redhat linux and mysql version: 3.23.58, I am using a left join statement:

SELECT _wy_short.*, t_specialty FROM _wy_short LEFT JOIN test_wy_short ON _wy_short.upin = test_wy_short.t_upin into outfile '/home/data/upin/_wy_short4.txt';

The outfile has an unknown character inserted at the join.

Running the statement without an outfile, the display is thrown off.

Downloading the outfile to windows, one text editor shows it as ?tab (questionmark tab), another text editor shows it as Ptab (linefeed tab). Microsoft word shows it as plain tab.

Fast SELECTs Versus Slow INSERTs With Indexes
I run a MySQL database using phpMyAdmin interfaced with an application written in C++, and most of my nine database tables have 500,000 - 2,000,000 records in them. These tables also have about five-to-six indexes each. This makes working with the C++ application extremely fast, which is nice. Any SELECT statements can be run in less than a second, and I'm very happy. The problem comes when I have to insert new records into the tables. Every morning, I am received a pipe-separated .txt file of the previous day's new records (about 500 - 1,000) that I have to insert into the tables using my C++ application. Well, this takes an excruciatingly long time (almost an hour, actually) and I need to find some way to make it run faster. I am thinking of several options here:

Error 1064 When Trying To Do Multiple Inserts In One Databse Call
I'm just in the process of switching over to mySQL from MSSQL. I'm trying to convert a web app I developed for my company to the mySQL format and I thought that the SQL queries would be the least of my worries.

I'm having problems with a part of the app where I insert multiple records at once into the database. To limit calls to the database from the web page, I bundle all the insert statements up into one string and then execute them at the same time. This approach works fine in MSSQL, but mySQL throws up the following error:

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 ';INSERT INTO tblTasks (TaskName, TaskDescription, TaskPriority, ProjectID) VALUE' at line 1

The SQL statement I'm trying to execute is the following:

INSERT INTO tblTasks (TaskName, TaskDescription, TaskPriority, ProjectID) VALUES ('Task A', 'Task A Description', 7, 6);INSERT INTO tblTasks (TaskName, TaskDescription, TaskPriority, ProjectID) VALUES ('Task B', 'Task B Description', 8, 6);

Is this the right way to go about it? There could be a better way to do these inserts for all I know. There's instances in the web app where multiple updates and deletes are also bundled together to limit database calls. Maybe I'm missing something simple, or maybe this was just a nice feature of MSSQL. Does anyone have any ideas?

I'm currently using mySQL 5 by the way, and am transferring from MSSQL 2000.

Dumpfile From Mysqldump Is Merging Rows For Multiple Inserts.
I am able to use mysqldump to view all the individual insertions of a table on an individual row. I have seen this occur before. Below is the desired format:

INSERT INTO `database` VALUES (1,...,...);
INSERT INTO `database` VALUES (2,...,...);
INSERT INTO `database` VALUES (3,...,...);

My dump producing the following instead:

INSERT INTO `database` VALUES (1,...,...),(2,...,...),(3,...,...);

Is there a way that I can configure the dump?

Store PDF In DB?
I am writing a web application that is actually an application for a school, causes some word confusion...
Anyway, applicants need to be able to upload PDF statements and letters of recommendation and such. The applicant can re-upload the file multiple times with corrections, but only the most recent version will be kept. When the application is finished the PDF will be accessed a few dozen times by various reviewers.
For easy backup/maintenance/organization it would make my life much simpler to store the PDFs directly in the database, however I gather that there may be problems with this. The only stats I can find seem to worry about scaling with dynamic content being loaded for thousands of visitors, however I don't need to worry about that as only a few dozen folks will want to view any given file, over several weeks.
Aside from scaling is there any other reason to avoid storing the PDFs in a BLOB? Will the performance hit be that significant?

How To Store
An user fills a form. My script generates a reference, call it REF. There's also another value which is the same for each REF, call it REF2. The script also generates a date value, which should be stored for each form filled and may have different values for the same REF, call it DATE. Then there's three more values which are come from what's filled in the form, so values may vary for the same REF, call them VAR1, VAR2 and VAR3. When I search the database, I will look for REF.

To sum up, each REF2 is always the same for one given REF; and DATE, VAR1, VAR2 and VAR3 need to be findable when I have the REF value.
By the way, all of these are VARCHARs (but for DATE which is a, well, DATE).

The Best Way To Store Sub Categories
I am setting up a system where users can select a main department and then a sub-category of that department. I currently have a department table that holds the main departments.

=============
id | department |
=============

If I put the sub-categories in the same table I am not sure of how to relate them to the main department. Is the best solution to put the sub-categories into another table with a department id column to reference the main department.

==========================
id | sub-category | departmentID |
==========================

Best Way To Store This Data
I'm doing a bit of a site where users can upload replays of games. The table (called 'replays') will have fields like id, name, date_uploaded .... which take up about 10 feilds.

But there also has to be a way to store data on each player. There can be up to 8 players in a game and the database must somehow record:

a) the name for each player
b) a rating for each player
c) and a second rating for each player.

Any tips on how to do this? I cant imagine having a database with 40+ feilds called player1rating1, player1rating2, player2rating1

Store Inserts/deletes

I need help writing a program that stores all the inserts, deletes and updates to my mysql database.