MYSQL :: Speed Of InnoDB DELETEs On Large Tables

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.

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).

How Speed Up Data Retrieve From Large Table
I try to retrieve duplicate data from database but it take a lot of time to display and very slow.Even using index also same .Here is my sql statement :

$sqlselect2 = "SELECT DISTINCT p1.resume_id, p1.user_id, p1.email, p1.name, p1.gender, p1.house_tel, p1.office_tel, p1.hand_tel, p1.new_ic, p1.old_ic, p1.address1, p1.address2, p1.city, p1.dob FROM resume_personal p1 ,resume_personal p2 where p1.$searchString=p2.$searchString And p1.resume_id!=p2.resume_id Order by p1.$sort $order ";

$result2 = mysql_query($sqlselect2,$conn) or die(mysql_error());

Anyones got the better solution to retrieve duplicate data or to improve the speed?

Mysqldump Speed On A DB With Thousands Of Tables
I am running Wordpress MultiUser (WPMU) to host a quite large french blog hosting network (close to 100000 users). WPMU creates 10 tables per user (basically duplicates wordpress'tables for each user).
This makes quite a lot of tables as you can imagine.
Now, I'm migrating my DB to a custom multi-DB setup, basically seperating users into multiple DBs instead of keeping them just in one.
Thing is, when i run mysqldump for just a few tables on the initial DB (trying to migrate one user's data for instance), the mysqldump takes *really long* to perform.
It takes me basically 1 hour to migrate 50 users, and considering the total number of users that's gonna take forever to move everyone around.

So, I've made some investigation, and using show processlist, I've seen that most of the time spent by mysqldump seemed to be running queries like
show table status like tablename
or
show table like tablename

These are probably taking very long due to the large number of tables in the DB.
Is there a way I can make these faster ? Or just have mysqldump use something else at all ?

How To Improve The Speed On Joining Tables
I have 2 tables, one of them has 0.8M of rows
the other has 40k of rows

total size is ~400MB

How can I improve the speed of joining these 2 tables faster?
do a sort first or other ways before joining them?

Database Access Speed: More Tables Or More Fields In A Table?
I have a question: it is better to create a database where there are more tables or more fields in few tables? What I want is: high speed accessing, indexing and searching. So what do you suggest me?

InnoDB Tables And MyISAM Tables In ONE Database?
My application demands some advantages from InnoDB and some from MyISAM.

Is it attainable to have both InnoDB tables and MyISAM tables in ONE database?
Or else, getting views from two databases, one from InnoDB and the other from MyISAM?

If not possible, any other approaches will do the job?

Very Large Tables
I am looking at using mysql to store some particle physics data. So the idea is (for example) I would have a table with a list of events, then a table with a list of particle types (one event can have many particles) and so on. The problem is that quite often I want to calculate something for every single event, but this table might run into 10s of millions of events and many GB. Obviously it would be mad to load the whole result set into memory then iterate over it. My question is: is there any way to have the db push results to the users one (or a few) at a time in an effiecent way? I was thinking of something like an iterator that pulls the data from the disk one (or few) at a time. An alternative (but much less useful) method might be to hand the function I want to calculate to mysql, for example:
select myfunc(x,y,z,a,b,c) from events where energy>3;
But as far as I know there is no way of defining your own functions in SQL

Managing Large Tables
I recently inherited a database (version 5.0.18) that has 1 table constantly growing out of hand between 10GB -30GB, therefore making is difficult/impossible to query. Also, the Archive Storage Engine is not installed. Currently, the table is manually renamed (i.e. tablename_daterange) and a new table created. ....

Large DB Should Use Multiple Tables?
I have a social networking site similar to myspace, currently my table which links who is who's friend on my site is 2 weeks old and is at 300,000 rows but is only at 15mb in size, this is the most accessed table of all my tables.

My question for performance, as this table grows should it eventually make a new table? If so here is my current select code

select userid,friendid,status from friend_friend where userid='$temp[auto_id]' and status='1'

Could someone give me an example of how o select this from multiple tables if I add more for this table in the future?

Large Number Of Tables
I would like to ask whether it is feasible (or whether it makes sense) to run a PHP script that creates a number of tables for each user.
Basically, this script allows users to sign up for a free service, and for each user that signs up, the script creates 9 tables in the database to contain that user's details.
Is this a sensible way of containing user data, and what are the inherent problems this may cause especially when say are 1000 users signup?

Slow Join On Large Tables
I have two tables:
D (500,000 recs), and DL (2,500,000 recs)

D has a PK and an index on HLQ. DL has a PK and an index on ID.

The following SQL:
SELECT
HLQ as "HLQ",
count(*)
FROM
D, DL
WHERE
D.DLID=DL.ID
GROUP BY HLQ

produces the following explain:
tabletypepossible_keyskeykey_lenrefrowsExtra
DALL500000Using where; Using temporary; Using filesort

DLeq_refIDID4D.DLID1Using index

The query takes ~ 3:30 on a Athlon xp2200; 1GB RAM; default bufer
settings.
Adding the following buffer settings only slightly decrerased the time
(~3:00).
key_buffer=512M
table_cache=256
sort_buffer=16M
read_buffer_size=16M

It appeasrs that the 'Using filesort' on table D is due to the Group
By clause and is the problem. I have an index on HLQ. Is there any
way to get MySQL to use it?

One Large Table Or Many Small Tables?
I'm trying to decide whether to use one large table or many small tables.
I need to gather information from various devices (about 500). Each device
has its own Id and some data.

Should I use only one table with an indexed column for the ID and another
column for the data, or should I use 500 tables each with only one column
for the data?

Slow Join On Large Tables
I have two tables:

D (500,000 recs), and DL (2,500,000 recs)

D has a PK and an index on HLQ. DL has a PK and an index on ID.

The following SQL:

SELECT
HLQ as "HLQ",
count(*)
FROM
D, DL
WHERE
D.DLID=DL.ID
GROUP BY HLQ

produces the following explain:
tabletypepossible_keyskeykey_lenrefrowsExtra
DALL500000Using where; Using temporary; Using filesort

DLeq_refIDID4D.DLID1Using index

The query takes ~ 3:30 on a Athlon xp2200; 1GB RAM; default bufer settings. Adding the following buffer settings only slightly decrerased the time (~3:00).

key_buffer=512M
table_cache=256
sort_buffer=16M
read_buffer_size=16M

It appeasrs that the 'Using filesort' on table D is due to the Group
By clause and is the problem. I have an index on HLQ. Is there any
way to get MySQL to use it?

Join Tables On A Large Database 200 Meg
i am trying to work out the most efficient way to list say multipl=
e categories of entries, the database is quite large about 200 meg.=20

I would like to know if using join tables is more efficient than storing th=
e keys in a varchar field then within the second loop doing a where in (1,2=
,3,4) where the 1,2,3,4 are the stored category keys in the varchar field =
rather than a where in (1), where 1 is the pirmaryID of the entry for insta=
nce ?

Optimizing Mysql For Large Tables
I have been working with Mysql for about 5 years - mainly in LAMP shops. The tables have been between 20-100 thousand records size. Now I have a project where the tables are in the millions of records.

This is very new to me and I am noticing that my queries are really slowwwwww!

What are the options that I have to speed my queries on the mysql side with regards to the my.cnf file. I have a fair understanding of sql optimization and I understand explain. I just want to see if there is something that I can do with the server also.

Optimizing Mysql For Large Tables
I have been working with Mysql for about 5 years - mainly in LAMP shops. The
tables have been between 20-100 thousand records size. Now I have a project
where the tables are in the millions of records.

This is very new to me and I am noticing that my queries are really
slowwwwww!

What are the options that I have to speed my queries on the mysql side with
regards to the my.cnf file. I have a fair understanding of
sql optimization and I understand explain. I just want to see if
there is something that I can do with the server also.

Thanks to all.

#Joseph Norris (Perl - what else is here?/Linux/CGI/Mysql)
print @c=map chr $_+100,(6,17,15,16,-68,-3,10,11,16,4,1,14,-68,12,1,14,8,
-68,4,-3,-1,7,1,14,-68,-26,11,15,1,12,4,-68,-22,11,14,14,5,15,-90);

One Large Table Vs. Many Small Tables
I'm working on a design using PHP & MySQL and I'd like to get some opinions on this.

My design has several tables that will be referenced but I'm wondering if those tables should be broken down even more and referenced more dynamically. The reason that I wonder about this is for long term goals. I hope that eventually there will be two or three thousand records that will be used on a regular basis. These records will need to be separated into groups, but I'm not sure if I should use a field in the database table or create a new table for each group.

If a few hundred records could be in each group, do you think it's better to use one large table with a field for the group ID, or a new table for each group?

Can't Populate Large Tables Through PhpMyAdmin
I'm having trouble woth Yahoo php hosting.
My client wants to move his websites from H-Sphere reseller company where (everything works fine) to YAHOO (he thinks that service and reliability will be much better?).
The problem is with 'products' table where are 15000 records.
Yahoo's phpMyAdmin times out if I try to upload sql/txt file (3.5 mg). I can insert smaller tables pasting script into SQL window - no problem with 4000 records)
I can't do anything with products table (15000 records).
I did not have this problem with our current hosting company.
Under the upload window it says that txt file can be up to 10 mb but mine is only 3.5 mb.

One Large Table Or Several Smaller Tables
From a speed of access standpoint, is it better to have one large table or several smaller tables? I'm in the early stages of development, so I can go either direction with this.

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?

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

Selecting From Multiple Large Tables Quickly
My skills with MySQL typically end at "SELECT * FROM table WHERE stuff", so I've had a lot of trouble with optimizing this query. I've tried doing multiple select statements for this, but that usually comes out with an average execution time of 99s. This method has a execution time of 6s still, so I'd like to get it down.

I have 4 important tables here (I'm only posting the columns which I use):

`auth`

UID mediumint(8) unsigned PRI NULL auto_increment
username varchar(32) MUL
clan_id mediumint(8) unsigned MUL 0
clan_abbrev varchar(7) MUL
`clans`

ID mediumint(8) unsigned PRI NULL auto_increment
Abbrev varchar(7) MUL
Name varchar(64)
icon_id smallint(5) unsigned 0
`stats`

UID int(10) unsigned PRI 0
kills mediumint(9) NULL
`usernames`

UID int(10) unsigned 0
ID int(10) unsigned PRI NULL auto_increment
Name varchar(32)
Uses mediumint(9) 0
`auth`, `stats`, and `usernames` are linked by `UID`. `clans` is linked to a row in `auth` based on it's `clan_id`.

I need to search the `usernames` table for a `Name` which matches a certain text (I use "storm" in my example below). I need to return a result with matches to that search, with each row containing:The `UID` which links the tables together for that user.The matching `Name` from `usernames`The matching `username` from `auth`The `Uses` from `usernames`.The `clan_id` from `auth`.The `Abbrev` from `clans`.The `Name` from `clans`.The `icon_id` from `clans`.The `kills` from `stats`.
The result should be ordered by `Uses` (from `usernames`) -- highest to lowest.

The tricky part is that some users may have Ɔ' as their `clan_id` in the `auth` table, in which case the clan id, abbrev, name, and icon_id should all be blank (0 or '' based on the type).

Here's what I managed to hammer out with my limited knowledge of SQL:

(
SELECT usernames.UID AS `UID` , usernames.Name AS `result` , auth.username AS `playername` , usernames.Uses AS `Uses` , auth.clan_id AS `clan_id` , clans.Abbrev AS `clan_abbrev` , clans.Name AS `clan_name` , clans.icon_id AS `clan_icon` , stats.kills AS `kills`
FROM `usernames` , `auth` , `clans` , `stats`
WHERE usernames.Name LIKE '%storm%'
AND auth.UID = usernames.UID
AND clans.ID = auth.clan_id
AND stats.UID = auth.UID
)
UNION (

SELECT usernames.UID AS `UID` , usernames.Name AS `result` , auth.username AS `playername` , usernames.Uses AS `Uses` , 0 AS `clan_id` , '' AS `clan_abbrev` , '' AS `clan_name` , 0 AS `clan_icon` , stats.kills AS `kills`
FROM `usernames` , `auth` , `stats`
WHERE usernames.Name LIKE '%storm%'
AND auth.UID = usernames.UID
AND auth.clan_id =0
AND stats.UID = auth.UID
)
ORDER BY `Uses` DESC
It works great, but the average query takes around 6s (these tables have several thousand entries in each).

Time Out Message When Query Large Tables
I'm trying to get data from 6 large tables but the volume of data in each table is too large and even select * from one of them make the system stop. I have afew questions:

1-what can I do to avoid the system stop or time out message?

2- To use several tabels infomation should I use 'View' command or can I use other methods?

3-I need to create a new table and insert the result from query in it. If I use the "view" can I insert the result of the view in a table?

(I use postgresql).

Speeding Up Large MySQL MyISAM Tables
I'm somewhat new to MySql. I've been using it for a while, but pretty much
out of the box setup, and am starting to suffer heavily with my larger
tables. I have a table with 5,000,000+ rows that I have to search and do
joins on. Although I have an index set up for it, the joined select will
still take some 400+ seconds to return, which is obviously unacceptable.
This is due to enormous HD access.

Perhaps someone can help me with indexes here; I was under
the impression that the index for the tables are cached in memory, and
therefore permitted "instant" searchability, without having to retrieve data
from each of the rows of the DB. Is there a startup parameter, or
something in the mysql.ini file, that must be set to allow for this? I have
the index configured properly, and have made sure that the query uses there
parameters in the where clause in the same order that they appear in the
index. Code:

Loading Large Tables From Files (wikipedia)
I'm an Oracle DBA and new to MySQL.
I was trying to load a 5.6 GB xml file onto mysql database using mediawiki tools.
The performance decreased gradually and crashed at ~60% completion.
Now I'm planning to use xml2sql tools to convert the dumps to txt format.
Then I'm planning to load this using mysqlimport.
I would like to run this load faster, and on Oracle, I would use the IMP parameters
such as INDEXES=n and so on. What are the recommended steps when using mysql?
Should I target any of the system variables?
I have a 4G ram on the server and can use all of it for the load.
Any pointers?

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.

Innodb Tables
I'm new to Unix and Mysql and I can't seem to get my innodb tables up and running.

My.cnf file looks like this:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysql.pid

I tried copying the portion of the my-medium.cnf that deals with innodb and putting it under the [mysqld] part but I got the can;t connect to socket error.

Copying InnoDB Tables
When using MyISAM tables, all the data and structure files are contained in the one directory - c:mysqldatamydatabase1, for instance. This means that to copy the files from one PC to another is easy.

However, if using InnoDB tables, you have the ibdata1, ib_logfile0 etc. in the c:mysqldata directory, which presumably contain data for all the tables in all the subdirectories.

As a developer, I often want to copy databases and data from clients so that I can work on them directly, but having combined data files would appear to make this impossible, since my overall database will be different from theirs.

Have I missunderstood how the ibdata1, ib_logfile0 etc. work, or is there a simple way to copy InnoDB data files? I have found reference to this on the MySQL site and forums, but nothing that addresses this particular issue.

Enableing InnoDB Tables
I am running SuSE 9.0. I have installed, MySql 4.0.15. Apparantly versions
4.0 and above are supposed to come with InnoDB tables already enabled, this
is not the case with mine, the have_innodb variable is at NO.

I edit the my.cnf file to include the following as per mysql manual

innodb_data_file_path = ibdata1:10M:autoextend
set-variable = innodb_buffer_pool_size=70M
set-variable = innodb_additional_mem_pool_size=10M
set-variable = innodb_log_file_size=20M
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1

However when I try and start mysqld up again I get error messages saying
that all the variables are not recognised. These variables are mentioned
again and again in help files to enable innodb tables in MySql.

Apache2triad 1.2 - InnoDB Tables
I am using Apache2triad 1.2 as local development environment. It is
working fine, but I cannot create InnoDB tables with the phpMyAdmin
2.5.6 included in this package.

According to the my.ini file (see below) some lines in this file must
be commented or uncommented to use innoDB. If I do this, the mySQL
server does not start any more. Code:

Innodb Tables In FreeBSD?
I'm creating some tables similar to this:

CREATE table clients (
client_id int(10) unsigned NOT NULL auto_increment,
birthdate date NOT NULL default '0000-00-00',
lastname char(15) NOT NULL,
firstname char(15) NOT NULL,
PRIMARY KEY (client_id),
KEY birthdate(birthdate)
);

Then I run mysqldump to see what was created. In windows I get:

ENGINE=InnoDB DEFAULT CHARSET=latin1;

but in FreeBSD I get:

ENGINE=MyISAM DEFAULT CHARSET=latin1;

How can I caulse FreeBSD to default to InnoDB tables also? I can't find a configuration file to see if I can change the default.

Myiasm Vs. Innodb Tables
I'm not sure why or when you would use an Innodb table vs. a MYISAM table. Can someone tell me when and why you would use one vs. the other?

Creating Innodb Tables
I created table like this:

CREATE TABLE `user` (
`username` VARCHAR(30) NOT NULL,
`password` VARCHAR(30) NOT NULL,
`userlevel` INT NOT NULL,
PRIMARY KEY ( `username` )
) TYPE=INNODB;

this is clearly setting the table as innodb.

Cannot Browse Data In InnoDB Tables
At present, I am using MySQL version 5.0.20 which I update via my spring application. Inside my configuration file I recently changed from: <property name="dialect">org.hibernate.dialect.MySQLMyISAMDialect</property> to <property name="dialect">org.hibernate.dialect.MySQLInnoDBDialect</property>. I did this because I wanted my transactions to rollback if they were not commited properly. However the problem I am now having is that I cant view the data inside my InnoDB tables (via my phpMyAdmin page). Whenever I click on the 'browse' button I get the following message: "MySQL returned an empty result set". However, I know data is inside the tables because the 'browse' button is not dissabled. If there is no data in the tables the button become dissabled.

Differance Between InnoDB And MyISAM Tables ?
can anybody tell me the differance between the innidb and myisam tables
types .

Mixed MYISAM-INNODB Tables
Our main db was built and designed with standard MYISAM tables. So far, so good. Somewhere along the line, one of our past developers snuck some INNODB tables into the db. When I attempted to bring up a test server for a new developer using a dumped copy of the db, MySQL chokes on the first idb table when attempting to import it. What am I missing?

Differance Between InnoDB And MyISAM Tables ?
can anybody tell me the differance between the innidb and myisam tables
types .

Changing Tables From MyISAM To InnoDB
I have a database with about 20 or so tables, maybe a few thousand rows
in each. I am starting to do more complex things with my insertions etc,
and I want to start to use transactions, so I imagine I should change
some table types in the database from MyISAM to InnoDB.

I know that InnoDB tables are a completely different beast than MyISAM in
terms of data storage etc... ie: instead of using folders on the server,
everything is stored in a file (ibdata1).

Is there anything I should be aware of when changing a bunch of tables
from MyISAM to InnoDB? Any hidden gotchas like changing 'ibdata1' in any
way, table slow downs, index changes, or ANYTHING? Or can I just alter
the tables and go on my merry way?

Something tells me I need to start learning a bit more about that ibdata1
file.... is there a size limit or anything? It seems to me that because
MyISAM stores its data in directories on the server, that accessing the
data would be much quicker. Having all my data in that one file seems
like it would slow things down... does it?

New Database -- 'can't Fetch' Innodb Tables
After migrating my tables in from Access, I cleaned them up and had them working well in innoDB. But now I can't open them through MySQL Administrator. I can view the table names (in red); I can create a new innoDB table (and behold it can be found as a .frm with the others); but when I try to view existing tables I get the error,

Quote: MySQL Administrator Exception:
Cannot fetch table information.

NOT NULL Fields In INNODB Tables
My database is composed of INNODB tables such as...

CREATE TABLEuser
(
nameVARCHAR(255)NOT NULL

) TYPE = INNODB;

The NOT NULL qualifier doesn't work as I would expect. While it
doesn't allow me to do this:

insert user values (null);

It will let me do this:

insert user values ("");

I appreciate that one could argue that "" is different to NULL and
therefore there is nothing wrong with this behaviour. However, surely
there is no defending the following (which is also allowed):

update user set name=NULL;

Is there any way that I can prevent users from setting a field to NULL
or ""? I am running version 4.0.17-nt on Windows XP

Differences Between Myisam And Innodb Tables
what is the difference between myisam and innodb tables ??
And why is there a comment "innodb free: 4096 kb" since I have changed my
tables from myisam to innodb?
Is this working like Sybase, where you need to create devices for data
storage, or do I miss the idea completely here?

Differance Between InnoDB And MyISAM Tables
can anybody tell me the differance between the innidb and myisam tables
types .

Row Level Locking With InnoDB Tables
I am relatively new to MySql (4.0.14) but I have read through the relevent
documentation and am still confused about how row level locking behaves
with InnoDB tables.

I created a database with a single innodb table which has 2 columns, one
of which is indexed. The locking behavior I see when I test against this
database is that it uses row level locks if the "SELECT ... FOR UPDATE"
involves the indexed column, but uses table level locks if instead it
involves the non-indexed column.

For example, if I have 2 mysql clients that perform the following operations:

client1> set autocommit=0;
client1> begin;
client1> SELECT my_column FROM my_table WHERE my_column = 1 FOR UPDATE;
client2> set autocommit=0;
client2> begin:
client2> SELECT my_column FROM my_table WHERE my_column = 2 FOR UPDATE;

The above query by client2 will block if the column in question is not
indexed, implying that client1 has somehow locked the entire table, even
though client1 and client2 are selecting different rows.

Am I misconfiguring something, or does InnoDB simply only support row
level locking when you are selecting indexed rows?

Mysqldump And Restore Of Innodb Tables
We are on MySQL 4.0.21 on linux. We use InnoDB tables and foreign key
constraints extensively. The mysqldump backs up the database tables in
alphabetical order with foreign key constraints defined in the create
statement of each table. These foreign key constraints are violated at the
time of restore. We have tried the following two solutions...

(1) We have tried to backup the database tables in the order of their
dependencies. This works but the backup scripts need to be constantly
maintained as new tables are added/removed from the database.

(2) phpMyAdmin export does the database dump and puts the table constraints
as ALTER statements at the end of the dump. Unfortunately, the phpMyAdmin
dumps cannot be automated to the best of my knowledge.

Does anyone have a solution/script to backup databases with InnoDB tables
such that constraints will not be violated at the time of restore.

Can't Open File Error For Innodb Tables
I enabled/initialized innodb for my MySql database using innodb_data_file_path = ibdata1:10M:autoextend in my.cnf

i created tables.. but some tables(the rest are working fine) when i try tp access them is giving meCan't open file: tablename.InnoDB'. (errno: 1)

Question About Row Level Locking With InnoDB Tables
I am relatively new to MySql (4.0.14) but I have read through the relevent
documentation and am still confused about how row level locking behaves
with InnoDB tables.

I created a database with a single innodb table which has 2 columns, one
of which is indexed. The locking behavior I see when I test against this
database is that it uses row level locks if the "SELECT ... FOR UPDATE"
involves the indexed column, but uses table level locks if instead it
involves the non-indexed column.

For example, if I have 2 mysql clients that perform the following operations:

client1> set autocommit=0;
client1> begin;
client1> SELECT my_column FROM my_table WHERE my_column = 1 FOR UPDATE;
client2> set autocommit=0;
client2> begin:
client2> SELECT my_column FROM my_table WHERE my_column = 2 FOR UPDATE;

The above query by client2 will block if the column in question is not
indexed, implying that client1 has somehow locked the entire table, even
though client1 and client2 are selecting different rows.

Am I misconfiguring something, or does InnoDB simply only support row
level locking when you are selecting indexed rows?