MYSQL :: Query (select/insert/update)Slows When Table Grows

Index Slows Down The SELECT
I have a table with about 15 different columns, of various types. It has 25,000 rows. I have a web-interface that does SELECT on that table, and does have options of sorting by some of the columns.

When I introduce new indexes on some columns, it speeds things up, but on some columns is actually slows the SELECT... ORDER BY.... statement.

Is that normal? I thought indexes take space, but never slow anything down (except for INSERT, DELETE, UPDATE).

Query Slows Down After Multiple Executions
I am running mysql 4.1 and am working with multiple selects and deletes on a very large innoDB table (50 million records)

basically, i am doing a Select statement on this table based on quite a few joins with other tables with a LIMIT 10 in a c# application then doing some programming logic with the Dataset I get and finally performing a delete on these 10 records. This Select is looped over and over again until all the records are deleted and nothing is found using the select. The wierd thing is that the first 100 or so times it loops it executes the SELECT statement in about 1 seconds. Then all of a sudden the query begins to take a matter of minutes per execution.

The problem does not go away even with a server restart. The only thng I have found that solves the problem is simply waiting a matter of a few hours then I will be able to execute the query in the 1 second timeframe for another 100 cycles or so.

Select, Update, Insert. Efficiently?
First, I want to say that I didn't design the databases and I wish I could fix them, but I can't.

There is a table, s01_Baskets, that tracks the users basket throughout my store.
There is a table s01_StoreKeys, that holds the next ID for every field used in tables. In this case I'm interested in is the basket_id.

What I need to do is create a new "basket" for a customer. To do this I need to select the key for this basket_id:

SELECT
s01_StoreKeys.maxvalue
FROM
s01_StoreKeys
WHERE
s01_StoreKeys.type = 'Baskets'
Then I can "create" a basket by inserting a row:

INSERT INTO
s01_Baskets
(
s01_Baskets.session_id
, s01_Baskets.cust_id
, s01_Baskets.basket_id
, s01_Baskets.order_id
, s01_Baskets.order_proc
, s01_Baskets.last_update
, s01_Baskets.ship_id
) VALUES (
'".session_id()."'
, Ɔ'
, Ɔ'
, Ɔ'
, Ɔ'
, '".microtime()."'
, Ɔ'
)";
Then I have to update that field for next key for the next basket.

UPDATE
s01_StoreKeys
SET
s01_StoreKeys.maxvalue = '".$basket_id+1."'
WHERE
s01_StoreKeys.type = 'Baskets'
UGH. I'm not thrilled doing it like this (one bad query, or if another process jumps in at the wrong time, I've messed up the baskets....)

Is there a better query for this? I'm pretty sure I can do the insert and select as one, but I need the basket_id(+1) for the update also. I don't know of any way to do all three queries in one. Can anyone see a better way to do this queries (ignore the poor design)?

Convert UPDATE Query To INSERT
I have an SQL dump file of a sql database that was generated with the UPDATE export type. The file contains code for generating the various tables inside the database along with the UPDATE lines for the table data.

Is there a way to process the dump file such that all of the UPDATE instructions are converted to INSERT instructions? I have to restore the database from scratch so there are no existing table rows to update.

Insert And Update In The Same Query (mySQL-3.23.58)
I am using mySQL-3.23.58 at my school. Since triggers are not available is there a way to do an insert and an update in one query?

Insert And Update Entire Table
My website will modify records on my products table. But I need to occasionally update their qty and their prices without losing any changes already made by other users online (like qty sold).

I'm able to do that now. It works really well:

INSERT / UPDATE A Row By Its Position In The Table?
Is there a way to insert or do an update of a row (aka record) by its position in the table, for example, the 5th row of the table? I know you can select data in such a manner, using the LIMIT 4,1 clause, for example. Does this work with insert/update as well, and if so, what's an example SQL syntax?

SELECT (INSERT...) Query
Is there any way I can do an insert and select the auto_incremented id in one SQL command?

Select And Update In One Query
My remoteurl table has 3 columns: id, hits and url.

PHP

UPDATE remoteurl SET hits=hits+1 WHERE id=2

SELECT url FROM remoteurl WHERE id=2

Instead of using two queries I want to use one query which could select url and update hits column. Is it possible?

SELECT And UPDATE In One Query
Does there exist a way to do a SELECT and an UPDATE in one query?

What I am trying to do is building a shop that´s articles are saved in a mySQL db and I wanna count the impressions of each article that is shown by e.g. a search function.

would be something like that:

article no | text | category | impressions
1 | aaaa| 1 |1
2 | bdsd| 2 |1
3 | aaaa| 2 |1
4 | bdsd| 3 |1

and so on

now I want e.g. to search for all articles that are in category 2 AND add +1 to the impressions cell of those articles that are found.

I hope it´s understandable what I am trying to do. I guess it should be possible, but since I only so far used very simple queries I am not sure how to?

Select From Table Before Insert
I am trying to create a trigger that will select the value for an integer from the table on which the insert is occuring, increment it by one, and insert the value into the new row on the same table. Basically, we have a user referral id. I want to grab the rank of the referral user, increment it, and insert it into the rank of the new user. i cannot figure out how to do this for the life of me. Code:

Insert Into Table A Select
I am testing out the innodb table types and I think that this statement should be able to work according to the transaction model for Innodb tables.

create table a (c1 char(10) type=innodb;
insert into a values ('aaa');
commit;
insert into a select * from a;

I get the following error:

ERROR 1066: Not unique table/alias: 'a'

How To Select Something And Then Insert Into Another Table
I have two table, tesing and retail. They all have same column (field): id, item, price. how do I write a syntax to select id, item and price and then insert into retail table.

1 Simply Query Problem. Please Help With Insert Into 1 Table From Another Table
I have Table A that has some records already in and then I have Table B that does a few things and gets updated regularly.

With Table A, I want to get it updated every 8 hours or so with new data that has been inserted into Table B.

I will do this using the Cron.

But what would be the best insert query to use so that it does the process really quick.

The query that I have so far is:

Quote:

mysql_query("INSERT INTO table a (id, title, descrip) SELECT id, title, descrip FROM table b where app=1");

With the above query the id's match in both tables. But I only want the new records to be inserted into Table A where app=1 in Table B and the row is not already in Table A.

How can I add to the query so that it does this.

Select Data From One Table Insert To Other
Can anyone give me some hints about a query.
A query which takes a value from form field insert it to 1st table,
then select other table within that query to select name,email from
2nd table and insert it to the 1st table from 2nd table,but condition is
that in where clause it must check that dcode entered in the form value
must match with 2nd table dcode(dcode is field in table.).

CREATE TABLE, INSERT INTO With SELECT In Parentheses
Assuming a legal SELECT statement, this works fine:

CREATE TABLE Foo SELECT ...

but this does not:

CREATE TABLE Foo (SELECT ...)

This is a problem for me as I'd like to use the output of a
SELECT...UNION...ORDER BY statement as input to CREATE TABLE.

Same holds for INSERT INTO.

Is this a bug?

Mysql_isam.log File Grows Over 13GB
is there a way to limit the size of this mysql_isam.log file? How can I safely delete it?

Need To Use Same Table In Subquery As In Insert Query
I have this table called "testtab":

ID | value
----------
1 | 8
2 | 15
3 | 5

Field ID is INT autoincrement as usual for ID field, field value is common INT. I need to insert new record to it. The inserted value should be equal to the highest value +1. I tried to do something like this:

INSERT INTO testtab SET value=(select max(value) from testtab)+1
Mysql threw this error:

You can't specify target table for update in FROM clause
I searched internet and found that I cannot use the same table in a subqery of insert/delete/update query. So my question is: Is here some workaround for this? I know I could use two queries - one to find the maximum value and second to insert the value+1 into DB, but I would like to do it in a single query.

Query To Insert 2 Foreign Key Values In Table
I want to insert 2 foreign key values along with some other value into table ....

"update If Not Exit Insert" Query?
is it possible to do query "update record. if not exist, do insert" in mysql?

QUERY One Table And Update Another In ONE Statement?
ZIP_CODE:
zip_code
latitude
longitude

customers:
customer_id
...
zip_code
latitude
longitude

The lat and long in the customers table is blank. I want to lookup customers' zip code in the ZIP_CODE, get the related lat & long and update the customers table with that information. The result being that after this query is run, the customers table now has lat & long data.

Update Query Where Values Will Come From Other Table
I'm creating an update query which the value will come from another table.

I have here my current query which unfortunately makes the system hangs. Probably because of the query itself is not properly coded.

update boxes b inner join messages m
on b.ctnnumber = m.ctnno
set b.consigneerecv = m.CName,
b.consigneerecvdate = m.DateRcv,
b.phrecventered = "Y",
b.PhilStatus = "delivered",
b.prevreleasestatus = b.releasestatus,
b.releasestatus = "delivered",
b.PhilStatusDate = m.smsrecvdate,
b.phdelprice = "0.00",
b.phdelamt = "0.00",
b.recvrelation = m.Relation,
b.APRecventered = m.smsRecvDate
where b.consigneerecv = ''
or b.consigneerecv = 'NA'
or b.consigneerecv is null;

I'm thinking revising it so that it will not cause the system to hang but I don't know how. Guys please help me with this one. I also have this another idea which probably will not work. My idea was something like this:

Update table1 set table1.column1 = (select table2.column1 where table2.column1 = table1.column1),
table1.column2 = (select table2.column2 where table2.column1 = table1.column1), .....

Insert Multiple Rows With One Insert Stmt And Nested Select
I'm trying to insert several rows into a table using only one insert statement:

insert into component_feature values (select 3,1,sf.software_feature_id,1 from software_feature sf where sf.software_id = 1)

When I run the select statement alone, I get the result I want:

+---+---+---------------------+---+
| 3 | 1 | software_feature_id | 1 |
+---+---+---------------------+---+
| 3 | 1 | 0 | 1 |
| 3 | 1 | 1 | 1 |
| 3 | 1 | 2 | 1 |
+---+---+---------------------+---+

But when I run the complete statement I get:

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 'select 3,1,sf.software_feature_id,1 from software_feature sf where sf.software_i' at line 1

This query is part of a PHP application I'm building. I'd rather not have to write a PHP loop to do multiple inserts.

Multi-table Query In One Select - Possible?
Runnint Mysql 3.23.47

I have the following two tables:
backorder_notification
->product_code varchar
->email varchar
->date_added date
Products
->code varchar
->name varchar
->other stuff that is not important

Essencially - I want to get a count of the product codes that are in the
backorder_notification table ordered by count then by product name and
output the display:

MyISAM Vs InnoDB While Creating A Table Using Select Query
I have created a table using the select query.

****
for eg: create table table_name1 as select * from table_name2
****

now the created table is by default MyISAM format, what changes should i make to the query so that the created table is InnoDB.

Can I Use Select To Query Mulitple Data From Within A Field Of A Table?
I would be grateful if someone could tell me whether Select is appropriate to choose data within a field and separated by commas . I have made the foll. script. What I want to achieve, is below the script.....

SELECT Search Query - Table Join Required? Help Please!! (PHP + MySQL)
I have a search form that has:
- drop down with states (nsw, vic etc)
- drop down with all business categories (retail, commercial etc)
- keyword / postcode field (2000, or 'builders')

The user gets results returned from the business table filtered by state (mandatory), which category is selected (mandatory) and by keyword (optional) or postcode (optional).

If keyword / search phrase is given then it will do search of the keywords fields of the business table (has already been indexed) in the selected category only. If postcode it will return all businesses in that category in order of distance from the given postcode.

I have 5 tables (additional fields ommitted):

'state'
state_id, name

'businesses'
business_id, keywords, name, postcode, state

'postcodes'
fromPostcode, toPostcode, distanceKMS

'categories'
category_id, name

'business2category'
business_id, category_id

Please dont thing i'm just pawning off my work here!!
Basically, i've got this working already, but only just, and in a very long and convulted format. Its far too long to post here, but since i'm not fully versed in table joins, i've been searching individual tables (e.g. SELECT *,MATCH AGAINST etc), building arrays, searching arrays again, and then building results at the end to fit into the paginator. Now the search is taking too long to perform, and i need a leaner alternative. not to mention theres way too many lines of code, and i just know theres a better way.

There must be a very simple way to achieve the following searches using table joins, can anybody please help me with 3 search examples below so i can try to understand joins better?

Search 1:
State + Category only

Search 2:
State + Category + Keyword

Search 2:
State + Category + Postcode

If there is no postcode, the others still need to display the data
filtered by distance from a default postcode of 2000.

I'd really appreciate if anybody has a few minutes free to help out here, and hopefully teach me something about effective table joins and searching. I've omitted the extra fields and tables from the real structure, and just left the relevant ones above - if theres anyhing missing or not making sense please let me know and i'll fix up asap.

How To Insert Multiple Rows With 1 Insert Query
I am having a form on the front end which has for example 3 rows each with 3 columns. The user enters data in all the 3 rows. When he hits the add button these should get in the database. What insert query would I write to add all of them together to the database? Do I need to use some procedure?

UPDATE Or INSERT
Kind of worked myself into a weird place in my current application. Where I find myself at is with a single form that is supposed to handle both updates of existing rows and insertions of new rows. The way I want this to work is:
1) If the ID is specified and references a valid row, update that row
2) If the ID is specified but does not reference an existing row, insert a new row
3) If the ID is not specified, insert a new row

Now, (3) is easily done in my application - just test if the ID was specified by the user. Simple. Nothing to it.

What I'm wondering is if there is a way to do both (1) and (2) in a single MySQL query and receive feedback such that I can in my application inform the user whether an update or an insert took place. I could do this with two seperate queries (query for the ID; if I find the row update it, otherwise insert a new one), but I'd like to do it with a single one if possible (mostly because I want to expand my skills with complex SQL queries).

Update Or Insert Sql
I am using this query to update tableone based upon the values in tabletwo. I really like this query because it works very fast.

PHP

UPDATE tableone n, tabletwo c
   SET n.sec  = c.sec,
       n.cat = c.cat,
       n.published = 1
WHERE n.section = c.section
   AND n.category = c.category

This works great however I cant seem to get it to insert or append if the data already exists. It just overwrites the data.

My difficulty is that I need certain columns to be updated (overwriting the data) and other columns to be inserted or appeneded to (adding to the data).

Is a stored procedure the best way of doing this?

Or, is there a way of adapting my sql to cater for this?

Need To Use UPDATE Instead Of INSERT INTO
I need to UPDATE exisiting rows and populate
the fields using only the resources in this INSERT statement.

INSERT INTO cms.project_event (projectPhaseID,sortOrder) SELECT
cms.projectTemplate_eventTemplate.projectTemplatePhaseID,
cms.projectTemplate_eventTemplate.sortOrder
FROM cms.projectTemplate_eventTemplate WHERE
cms.projectTemplate_eventTemplate.projectTemplateID = '1'

Any clue pointing in the right direction will be of great help. Im not providing alot to go on just the to update the two fields from the other table instead of INSERTING new rows.

Last Update/insert
Can I know the time when the last UPDATE or INSERT occur in a table in a database?

UPDATE And INSERT In One Go?
Is it possible to create a query that will do an UPDATE or an INSERT depending on certain data already in the database?

Eg I'd like to record a user's vote. If this user hasn't voted already, an INSERT is called for, otherwise an UPDATE. Right now, I'm SELECTing first, checking whether the user's id is already in the votes table, and if yes, my query's an UPDATE, otherwise it's an INSERT.

This needs two hits on the DB however, and I'm someone who likes to limit the amount of DB hits as much as possible.

Update And/or Insert?
I'm using MySQL with PHP. I want to update a record if it already
exists. If it does not exist, I need to create a new record.

Is this done through a simple SQL command, or do I need to use PHP to
negotiate two separate UPDATE and INSERT statements?

After Insert, After Update
I am taking my first and last class in programming as I can't make sense of the textbook or teachers instruction (but I am determined to get at least an A or B in this class.) So if anyone could tell me in VERY simple terms how to write an after insert and after update trigger I would be very much grateful.

INSERT / UPDATE
I'm having trouble using the IF statement.
What I want is to do either a INSERT or a UPDATE depending on the SELECT statement.
No mather what i do, it gives me an error on the INSERT statement. Isn't allowed to have a INSERT/UPDATE statment inside a IF statement?

This is what i'm trying:

SET @param = (SELECT)

IF @param IS NULL THEN
INSERT...
ELSE
UPDATE...
END IF

INSERT Or UPDATE
I need using a SELECT statement, but is there a way to make this work on INSERT or UPDATE statements as well?

In other words, I can do:
SELECT MONTH(deadline) FROM projects ...

But can I do something like:
UPDATE projects SET MONTH(deadline)='11' ...
where the rest of the date stays the same, but only the month is updated?

Doesn't seem to work, and I don't see any examples in the manual comments, but it sure would be nifty.

Update Or Insert
I have to create a database with 8.000 entries.
It will be the top entries it will have. Now... what is faster ?
a) to create the table before with 8.000 rows and the id field from 1 to 8.000 (primary key...) and then do UPDATE's
b) to create the rows as they have to be created with INSERT's ?
In the middle of the process it will be SELECT's from other users.

Insert If I Can't Update
I check for the data to exist. If it does, I UPDATE, otherwise I INSERT. Is there a way to do it better, more efficiently?

UPDATE Or INSERT
I am looking for a solution to an update-type problem. Users enter name, address, etc etc. I have set the first_name & last_name fields to be jointly unique, so that no two records can exist with the same first and last names the same (e.g, no 2 records with 'Joe Bloggs' as first/last names.).
Now if users want to correct, say, their address, it will not allow them to since a record already exists with their first/last name.
Is there a SQL statement that will allow me to bypass the UNIQUEness constraints when other fields need to be updated?
(And related, if the UNIQUE fields are updated, it currently creates a new record but leaves the old one there. I need to overwrite the old record but keep the same ID number primary key.)

INSERT/UPDATE
I've got an array in PHP, which is identical to a row in mysql:

Code:

array
(
'key' => 'somekey',
'column1' => '...',
'column2' => '...',
'column3' => '...',
);

If 'somekey' does not exist in the table, I want to INSERT this array as a new row. Otherwise, I want to UPDATE the row which has the key 'somekey'.
Is it possible to do this in a single query? Cause now, I'm first INSERTing a row and if that fails, I try an UPDATE instead.

Insert/update
I have a table of x/y values and a set of 'new' x/y values.
For each of my new x values that already has a record in the table, I want to add the new y value to the corresponding existing y value.
If there is no x value in the table, I want to create a new record with the new x/y values.Is there a simple (atomic) way of doing this?

Insert Or Update If Exist
what is the sql statement that can insert or update the record if it exist.

Update / Insert / Delete Log
I'm wondering if there is any way to get a lot of recent update/insert/delete statements performed on a db by a particular user?

Insert Then Update That Record
I'm using PHP to insert contact information in to a database. Confidentiality requirements have made the design change so that the private information be entered separately. I've modified Form 1 so that it contains the public information and added a button. The button does two things, it inserts the record in to the database then draws the private information form.

My question is, can mysql give me the last row inserted so I can pass that information on to the form and use it in my update query? I tried doing it by ID in that table but the id is not assigned until after the submit button is pressed. I can do another query but thought it would be slick to do it by row.

Insert On Duplicate Key Update
Is it possible so do something like this

"insert * from TempTabel on dublicate key update 'all fields'; "

So I don't have to write every column name. I need this, so I can use the same code on multible tabels. I could use replace, but then the foreign key in other tables are not updated.

If Exists Then Update, Otherwise Insert
provide a very simple if, then, else clause in MySQL?
If a customer named 'bob' is already in the database, I want to update his id. If he is not already in the database, I want to insert him.
What would be the best way to do this?
I get errors for the following:
IF EXISTS
(SELECT 1 FROM customers WHERE name = 'bob')
THEN
UPDATE customers
SET id = '007' WHERE name = 'bob';
ELSE
INSERT INTO customers(id, customer)
VALUES('007', 'bob');
END IF