How can I get Hibernate (using JPA) to create MySQL InnoDB tables (instead of MyISAM)? I have found solutions that will work when using Hibernate to generate an SQL file to create the tables, but nothing that works &quot;on the fly&quot;.

Hibernate: Create Mysql InnoDB tables instead of MyISAM

I am using mysql and mysql workbench. I created 5 tables with innodb engine. I checked their engine and it was innodb before I insert data into them. I inserted data from 5 MyISAM tables and now my innodb tables are MyISAM. I can&#39;t change them. I used the alter table engine=innodb but it doesn&#39;t work.


Changing Table Engine in MySQL

I have an enormous MySQL (InnoDB) database with millions of rows in the sessions table that were created by an unrelated, malfunctioning crawler running on the same server as ours. Unfortunately, I have to fix the mess now.

If I try to `truncate table sessions;` it seems to take an inordinately long time (upwards of 30 minutes). I don&#39;t care about the data; I just want to have the table wiped out as quickly as possible. Is there a quicker way, or will I have to just stick it out overnight?

Quickest way to delete enormous MySQL table

I&#39;ve been working on writing SQL to create a MySQL database with several default options, including character set and collation.  Is it possible to set the default storage engine for tables in this database to InnoDB? 

I&#39;ve been looking through the MySQL 5.1 manual and I&#39;ve found the statement `ENGINE=innodb` which would be appended to a `CREATE TABLE` statement, but I haven&#39;t found anything related to a `CREATE DATABASE` statement.  

Is there a normal way to do this as part of the database creation, or does it need to be specified on a table-by-table basis?


How can I set default storage engine used by MySQL?

I am trying to increase the size of the innodb_buffer_pool_size in MySQL 5.1 as I keep running into the following error indicating I have run out of space for the table locks. 

    ERROR: The total number of locks exceeds the lock table size
    Error
    Code: 1206

I have gone through the documentation and from what I gather, I need to update innodb_buffer_pool_size in the /etc/my.cnf file. My current value is 8M. However, even after creating that file and adding the following line to set the value it is not updating in MySQL. 

    set-variable=innodb_buffer_pool_size=256M

Does have any advice on how I can adjust this value in MySQL on my mac? Any other advice or suggestions? 

How to change value for innodb_buffer_pool_size in MySQL on Mac OS?

Let me start by saying I have looked at many similar questions asked, but all of them relate to `Timestamp` and `DateTime` field type without indexing. At least that is my understanding. 

As we all know, there are certain advantages when it comes to DateTime. Putting them aside for a minute, and assuming table&#39;s engine is `InnoDB` with `10+ million records`, which query would perform faster when criteria is based on:

&gt;1. DateTime with index
&gt;2. int with index

In other words, it is better to store date and time as `DateTime` or UNIX timestamp in `int`? Keep in mind there is no need for any built-in MySQL functions to be used.

**Update**

Tested with MySQL 5.1.41 (64bit) and 10 million records, initial testing showed significant speed difference in favour of `int`. Two tables were used, `tbl_dt` with `DateTime` and `tbl_int` with `int` column. Few results:

    SELECT SQL_NO_CACHE COUNT(*) FROM `tbl_dt`;
    +----------+
    | COUNT(*) |
    +----------+
    | 10000000 |
    +----------+
    1 row in set (2 min 10.27 sec)

    SELECT SQL_NO_CACHE COUNT(*) FROM `tbl_int`;
    +----------+
    | count(*) |
    +----------+
    | 10000000 |
    +----------+
    1 row in set (25.02 sec)

    SELECT SQL_NO_CACHE COUNT(*) FROM `tbl_dt` WHERE `created` BETWEEN &#39;2009-01-30&#39; AND &#39;2009-12-30&#39;;
    +----------+
    | COUNT(*) |
    +----------+
    |   835663 |
    +----------+
    1 row in set (8.41 sec)

    SELECT SQL_NO_CACHE COUNT(*) FROM `tbl_int` WHERE `created` BETWEEN 1233270000 AND 1262127600;
    +----------+
    | COUNT(*) |
    +----------+
    |   835663 |
    +----------+
    1 row in set (1.56 sec)


I&#39;ll post another update with both fields in one table as suggested by [shantanuo][1].

**Update #2**

Final results after numerous server crashes :) Int type is significantly faster, no matter what query was run, the speed difference was more or less the same as results above.

&quot;Strange&quot; thing observed was execution time was more or less the same when two both field types are stored in the same table. It seems MySQL is smart enough to figure out when the values are the same when stored in both DateTime and int. Haven&#39;t found any documentation on the subject, therefore is just an observation.

 [1]: https://stackoverflow.com/users/139150/


MySQL Integer vs DateTime index

I know I can issue an alter table individually to change the table storage from MyISAM to InnoDB.

I am wondering if there is a way to quickly change all of them to InnoDB?

How to convert all tables from MyISAM into InnoDB?

In my production error logs I occasionally see:

&gt; SQLSTATE[HY000]: General error: 1205
&gt; Lock wait timeout exceeded; try
&gt; restarting transaction

I know which query is trying to access the database at that moment but is there a way to find out which query had the lock at that precise moment?

How to debug Lock wait timeout exceeded on MySQL?

In our production database, we ran the following pseudo-code SQL batch query running every hour:

    INSERT INTO TemporaryTable
        (SELECT FROM HighlyContentiousTableInInnoDb
         WHERE allKindsOfComplexConditions are true)

Now this query itself does not need to be fast, but I noticed it was locking up `HighlyContentiousTableInInnoDb`, even though it was just reading from it. Which was making some other very simple queries take ~25 seconds (that&#39;s how long that other query takes).

Then I discovered that InnoDB tables in such a case are actually locked by a SELECT! https://www.percona.com/blog/2006/07/12/insert-into-select-performance-with-innodb-tables/

But I don&#39;t really like the solution in the article of selecting into an OUTFILE, it seems like a hack (temporary files on filesystem seem sucky). Any other ideas? Is there a way to make a full copy of an InnoDB table without locking it in this way during the copy. Then I could just copy the `HighlyContentiousTable` to another table and do the query there.

How to improve INSERT INTO ... SELECT locking behavior

A co-worker just made me aware of a very strange MySQL behavior.

Assuming you have a table with an auto_increment field and another field that is set to unique (e.g. a username-field). When trying to insert a row with a username thats already in the table the insert fails, as expected. Yet the auto_increment value is increased as can be seen when you insert a valid new entry after several failed attempts.

For example, when our last entry looks like this...

    ID: 10
    Username: myname

...and we try five new entries with the same username value on our next insert we will have created a new row like so:

    ID: 16
    Username: mynewname

While this is not a big problem in itself it seems like a very silly attack vector to kill a table by flooding it with failed insert requests, as the MySQL Reference Manual states:
 &gt;&quot;The behavior of the auto-increment mechanism is not defined if [...] the value becomes bigger than the maximum integer that can be stored in the specified integer type.&quot;

Is this expected behavior?

Why does MySQL autoincrement increase on failed inserts?

I understand that this question has been asked before, but most of the time it is asked in relation to a specific database or table. I cannot find an answer on this site that describes the two engines and their differences without respect to someones specific database. 

I want to be able to make more informed decisions in the future with respect to designing a table or database, so am looking for a comprehensive answer on the differences between the two storage engines.

What&#39;s the difference between [MyISAM][1] and [InnoDB][2], and what should I be looking for when trying to decide between one or the other?


  [1]: http://en.wikipedia.org/wiki/MyISAM
  [2]: http://en.wikipedia.org/wiki/InnoDB

What&#39;s the difference between MyISAM and InnoDB?

The MySQL manual at [MySQL][1] covers this.

Usually I just dump the database and reimport it with a new name. This is not an option for very big databases. Apparently `RENAME {DATABASE | SCHEMA} db_name TO new_db_name;` [does bad things, exist only in a handful of versions, and is a bad idea overall][1].

This needs to work with [InnoDB][2], which stores things very differently than [MyISAM][3].

  [1]: http://web.archive.org/web/20160504181056/https://dev.mysql.com/doc/refman/5.1/en/rename-database.html
  [2]: http://en.wikipedia.org/wiki/InnoDB
  [3]: http://en.wikipedia.org/wiki/MyISAM


How do I quickly rename a MySQL database (change schema name)?

I&#39;m working on a projects which involves a lot of database writes, I&#39;d say (*70% inserts and 30% reads*).  This ratio would also include updates which I consider to be one read and one write.  The reads can be dirty (e.g. I don&#39;t need 100% accurate information at the time of read).  
The task in question will be doing over 1 million database transactions an hour.  

I&#39;ve read a bunch of stuff on the web about the differences between MyISAM and InnoDB, and MyISAM seems like the obvious choice to me for the particular database/tables that I&#39;ll be using for this task.  From what I seem to be reading, InnoDB is good if transactions are needed since row level locking is supported.

Does anybody have any experience with this type of load (or higher)?  Is MyISAM the way to go?

MyISAM versus InnoDB

Per [the MySQL docs][1], there are four TEXT types:

 1. TINYTEXT
 2. TEXT
 3. MEDIUMTEXT
 4. LONGTEXT

What is the maximum length that I can store in a column of each data type assuming the character encoding is UTF-8?


  [1]: http://dev.mysql.com/doc/refman/5.7/en/blob.html

TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT maximum storage sizes

Is there a measurable performance difference between using INT vs. VARCHAR as a primary key in MySQL? I&#39;d like to use VARCHAR as the primary key for reference lists (think US States, Country Codes) and a coworker won&#39;t budge on the INT AUTO_INCREMENT as a primary key for all tables. 

My argument, as detailed [here][1], is that the performance difference between INT and VARCHAR is negligible, since every INT foreign key reference will require a JOIN to make sense of the reference, a VARCHAR key will directly present the information.

So, does anyone have experience with this particular use-case and the performance concerns associated with it?

  [1]: http://database-programmer.blogspot.com/2008/01/database-skills-sane-approach-to.html#rule1

Is there a REAL performance difference between INT and VARCHAR primary keys?

I want to use foreign keys to keep the integrity and avoid orphans (I already use innoDB).

How do I make a SQL statment that DELETE ON CASCADE?

If I delete a category then how do I make sure that it would not delete products that also are related to other categories.

The pivot table &quot;categories_products&quot; creates a many-to-many relationship between the two other tables.

    categories
    - id (INT)
    - name (VARCHAR 255)
    
    products
    - id
    - name
    - price
    
    categories_products
    - categories_id
    - products_id




MySQL foreign key constraints, cascade delete

I have one MySQL table using the InnoDB storage engine; it contains about 2M data rows. When I deleted data rows from the table, it did not release allocated disk space.  Nor did the size of the ibdata1 file reduce after running the `optimize table` command.

Is there any way to reclaim disk space from MySQL?

I am in a bad situation; this application is running in about 50 different locations and now problem of low disk space is appearing at almost all of them.

MySQL InnoDB not releasing disk space after deleting data rows from table

In MySQL, how do I get a list of all foreign key constraints pointing to a particular table? a particular column? This is the same thing as [this Oracle question][1], but for MySQL.

  [1]: https://stackoverflow.com/questions/85978/query-a-tables-foreign-key-relationships

How do I see all foreign keys to a table or column?

I&#39;m trying to add a row to an `InnoDB` table with a simply query:

    INSERT INTO zip_codes (zip_code, city) VALUES (&#39;90210&#39;, &#39;Beverly Hills&#39;);

But when I attempt this query, I get the following:

&gt; ERROR 1114 (HY000): The table `zip_codes` is full

Doing a 

    SELECT COUNT(*) FROM zip_codes

gives me 188,959 rows, which doesn&#39;t seem like too many considering I have another table with 810,635 rows in that same database.

I am fairly inexperienced with the `InnoDB engine` and never experienced this issue with `MyISAM`.  What are some of the potential problems here ?

EDIT: This only occurs when adding a row to the `zip_codes` table.

1114 (HY000): The table is full

Is it possible to clean a mysql innodb storage engine so it is not storing data from deleted tables?

Or do I have to rebuild a fresh database every time?

Howto: Clean a mysql InnoDB storage engine?

What is `InnoDB` and `MyISAM` in `MySQL` ?

What is InnoDB and MyISAM in MySQL?

I&#39;m just about to write a query that includes a `WHERE isok=1`. As the name implies, `isok` is a boolean field (actually a `TINYINT(1) UNSIGNED` that is set to 0 or 1 as needed).

Is there any performance gain in indexing this field? Would the engine (InnoDB in this case) perform better or worse looking up the index?

Is there any performance gain in indexing a boolean field?

I am using MySQL in localhost as a &quot;query tool&quot; for performing statistics in R, that is, everytime I run a R script, I create a new database (A), create a new table (B), import the data into B, submit a query to get what I need, and then I drop B and drop A.

It&#39;s working fine for me, but I realize that the ibdata file size is increasing rapidly, I stored nothing in MySQL, but the ibdata1 file already exceeded 100 MB.

I am using more or less default MySQL setting for the setup, is there a way for I can automatically shrink/purge the ibdata1 file after a fixed period of time?

How to shrink/purge ibdata1 file in MySQL

I get this error message:

&gt; ERROR 1217 (23000) at line 40: Cannot
&gt; delete or update a parent row: a
&gt; foreign key constraint fails

... when I try to drop a table:

    DROP TABLE IF EXISTS `area`;

... defined like this:

    CREATE TABLE `area` (
      `area_id` char(3) COLLATE utf8_spanish_ci NOT NULL,
      `nombre_area` varchar(30) COLLATE utf8_spanish_ci NOT NULL,
      `descripcion_area` varchar(100) COLLATE utf8_spanish_ci NOT NULL,
      PRIMARY KEY (`area_id`),
      UNIQUE KEY `nombre_area_UNIQUE` (`nombre_area`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;

The funny thing is that **I already dropped all other tables** in the schema that have foreign keys against `area`. Actually, the database is empty except for the `area` table.

How can it possibly have child rows if there isn&#39;t any other object in the database? As far as I know, InnoDB doesn&#39;t allow foreign keys on other schemas, does it?

(I can even run a `RENAME TABLE area TO something_else` command :-?)

Bogus foreign key constraint fail

Is there a nice easy way to drop all tables from a MySQL database, ignoring any foreign key constraints that may be in there?

MySQL DROP all tables, ignoring foreign keys

We&#39;re considering using UUID values as primary keys for our MySQL database. The data being inserted is generated from dozens, hundreds, or even thousands of remote computers and being inserted at a rate of 100-40,000 inserts per second, and we&#39;ll never do any updates.

The database itself will typically get to around 50M records before we start to cull data, so not a massive database, but not tiny either. We&#39;re also planing to run on InnoDB, though we are open to changing that if there is a better engine for what we&#39;re doing.

We were ready to go with Java&#39;s Type 4 UUID, but in testing have been seeing some strange behavior. For one, we&#39;re storing as varchar(36) and I now realize we&#39;d be better off using binary(16) - though how much better off I&#39;m not sure.

The bigger question is: how badly does this random data screw up the index when we have 50M records? Would we be better off if we used, for example, a type-1 UUID where the leftmost bits were timestamped? Or maybe we should ditch UUIDs entirely and consider auto_increment primary keys?

I&#39;m looking for general thoughts/tips on the performance of different types of UUIDs when they are stored as an index/primary key in MySQL. Thanks!

UUID performance in MySQL?

I&#39;m developing a high-volume web application, where part of it is a MySQL database of discussion posts that will need to grow to 20M+ rows, smoothly.

I was originally planning on using MyISAM for the tables (for the built-in [fulltext search capabilities][1]), but the thought of the *entire table* being locked due to a single write operation makes me shutter. Row-level locks make so much more sense (not to mention InnoDB&#39;s other speed advantages when dealing with huge tables). So, for this reason, I&#39;m pretty determined to use InnoDB.

The problem is... InnoDB doesn&#39;t have built-in fulltext search capabilities.

Should I go with a third-party search system? Like [Lucene(c++)][2] / [Sphinx][3]? Do any of you database ninjas have any suggestions/guidance? &lt;del&gt;LinkedIn&#39;s [zoie][4] (based off Lucene) looks like the best option at the moment&lt;/del&gt;... having been built around realtime capabilities (which is pretty critical for my application.) I&#39;m a little hesitant to commit yet without some insight...

(FYI: going to be on EC2 with high-memory rigs, using PHP to serve the frontend)

  [1]: http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html
  [2]: http://sourceforge.net/projects/clucene/
  [3]: http://www.sphinxsearch.com/
  [4]: http://code.google.com/p/zoie/

Fulltext Search with InnoDB

In MySQL, there is no way to specify a storage engine for a certain database, only for single tables.  However, you can specify a storage engine to be used during one session with:

    SET storage_engine=InnoDB;

So you don&#39;t have to specify it for each table.

**How do I confirm, if indeed all the tables are using InnoDB?**

How do I know if a mysql table is using myISAM or InnoDB Engine?

My MySQL database contains several tables using different storage engines
(specifically myisam and innodb).  How can I find out which tables are
using which engine?

How can I check MySQL engine type for a specific table?

I was using mysqldump to export the database, like this:

    mysqldump -u root -ppassword my_database &gt; c:\temp\my_database.sql

Somehow, it only exports one table. Is there something I&#39;m doing wrong?

mysqldump exports only one table

So I am trying to add a primary key to one of the tables in my database. Right now it has a primary key like this:

    PRIMARY KEY (user_id, round_number)

Where user_id is a foreign key.

I am trying to change it to this:

    PRIMARY KEY (user_id, round_number, created_at)

I am doing this in phpmyadmin by clicking on the primary key icon in the table structure view.

This is the error I get:

    #1025 - Error on rename of &#39;./database/#sql-2e0f_1254ba7&#39; to &#39;./database/table&#39; (errno: 150)

It is a MySQL database with InnoDB table engine.

#1025 - Error on rename of &#39;./database/#sql-2e0f_1254ba7&#39; to &#39;./database/table&#39; (errno: 150)

I&#39;m using MySQL&#39;s AUTO_INCREMENT field and InnoDB to support transactions. I noticed when I rollback the transaction, the AUTO_INCREMENT field is not rollbacked? I found out that it was designed this way but are there any workarounds to this? 

MySQL AUTO_INCREMENT does not ROLLBACK

I&#39;m trying to parse the MySQL [docs](http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html). They could be clearer. What they seem to be saying is that there are five possibilities: SET NULL, NO ACTION, RESTRICT, CASCADE, and SET DEFAULT.

NO ACTION and RESTRICT do the same thing (prevent any DB change that breaks an FK) and that thing is the default so if you omit an ON DELETE clause you&#39;re saying NO ACTION (or RESTRICT -- same thing).

SET NULL allows a parent row deletion, sets the FK to NULL.

CASCADE deletes child row.

SET DEFAULT should just never be used.

Is this more or less correct?

What is MySQL&#39;s default ON DELETE behavior?

I would like to know if it&#39;s possible in InnoDB in `MySQL` to have a `table` with **foreign key** that references another table in a different `database` ? 

And if so, how this can be done ?

MySQL InnoDB foreign key between different databases

I know it&#39;s generally a bad idea to do queries like this:

    SELECT * FROM `group_relations`

But when I just want the count, should I go for this query since that allows the table to change but still yields the same results.

    SELECT COUNT(*) FROM `group_relations`

Or the more specfic

    SELECT COUNT(`group_id`) FROM `group_relations`

I have a feeling the latter could potentially be faster, but are there any other things to consider?

**Update**: I am using InnoDB in this case, sorry for not being more specific.

Should I COUNT(*) or not?

I looked around and found some of the MySQL engines are innodb and MyISAM. Perhaps there are few more. My question is what are these database engines?

What are the differences between different MySQL engines? And more importantly, How do I decide which one to use?

What are MySQL database engines?

I have experience with MS SQL server where it is possible and useful to [update statistic][1] and [rebuild indexes][2]. I can&#39;t find such option in MySQL innoDB, is there such option? If not, how MySQL database create an execution plan? Does the MySQL update indexes and statistic with every UPDATE and INSERT?


  [1]: https://msdn.microsoft.com/en-us/library/ms187348.aspx?f=255&amp;MSPPError=-2147217396
  [2]: https://msdn.microsoft.com/en-us/library/ms189858.aspx

How can I rebuild indexes and update stats in MySQL innoDB?

I have  a set of `InnoDB` tables that I periodically need to maintain by removing some rows  and inserting others.  Several of the tables have foreign key constraints referencing other tables, so this means that the table loading order is important.  To insert the new rows without worrying about the order of the tables, I use:

    SET FOREIGN_KEY_CHECKS=0;

before, and then:

    SET FOREIGN_KEY_CHECKS=1;

after.  

When the loading is complete, I&#39;d like to check that the data in the updated tables still hold referential integrity--that the new rows don&#39;t break foreign key constraints--but it seems that there&#39;s no way to do this.

As a test, I entered data that I was sure violated foreign key constraints, and upon re-enabling the foreign key checks, mysql produced no warnings or errors.

If I tried to find a way to specify the table loading order, and left the foreign key checks on during the loading process, this would not allow me to load data in a table that has a self-referencing foreign key constraint, so this would not be an acceptable solution.

Is there any way to force InnoDB to verify a table&#39;s or a database&#39;s foreign key constraints?

Force InnoDB to recheck foreign keys on a table/tables?

I am using large random numbers as keys (coming in from another system).  Inserts and updates on fairly-small (as in a few million rows) tables are taking much longer than I think is reasonable.

I have distilled a very simple test to illustrate.  In the test table I&#39;ve tried to make it as simple as possible; my real code does not have such a simple layout and has relations and additional indices and such.  However, a simpler setup shows equivalent performance.

Here are the results:

    creating the MyISAM table took 0.000 seconds
    creating 1024000 rows of test data took 1.243 seconds
    inserting the test data took 6.335 seconds
    selecting 1023742 rows of test data took 1.435 seconds
    fetching 1023742 batches of test data took 0.037 seconds
    dropping the table took 0.089 seconds
    creating the InnoDB table took 0.276 seconds
    creating 1024000 rows of test data took 1.165 seconds
    inserting the test data took 3433.268 seconds
    selecting 1023748 rows of test data took 4.220 seconds
    fetching 1023748 batches of test data took 0.037 seconds
    dropping the table took 0.288 seconds

Inserting 1M rows into MyISAM takes 6 seconds; into InnoDB takes **3433 seconds**!

What am I doing wrong?  What is misconfigured?  (MySQL is a normal Ubuntu installation with defaults)

Here&#39;s the test code:

    import sys, time, random
    import MySQLdb as db
    
    # usage: python script db_username db_password database_name
    
    db = db.connect(host=&quot;127.0.0.1&quot;,port=3306,user=sys.argv[1],passwd=sys.argv[2],db=sys.argv[3]).cursor()
    
    def test(engine):
    
        start = time.time() # fine for this purpose
        db.execute(&quot;&quot;&quot;
    CREATE TEMPORARY TABLE Testing123 (
    k INTEGER PRIMARY KEY NOT NULL,
    v VARCHAR(255) NOT NULL
    ) ENGINE=%s;&quot;&quot;&quot;%engine)
        duration = time.time()-start
        print &quot;creating the %s table took %0.3f seconds&quot;%(engine,duration)
        
        start = time.time()
        # 1 million rows in 100 chunks of 10K
        data = [[(str(random.getrandbits(48)) if a&amp;1 else int(random.getrandbits(31))) for a in xrange(10*1024*2)] for b in xrange(100)]
        duration = time.time()-start
        print &quot;creating %d rows of test data took %0.3f seconds&quot;%(sum(len(rows)/2 for rows in data),duration)
        
        sql = &quot;REPLACE INTO Testing123 (k,v) VALUES %s;&quot;%(&quot;(%s,%s),&quot;*(10*1024))[:-1]
        start = time.time()
        for rows in data:
            db.execute(sql,rows)
        duration = time.time()-start
        print &quot;inserting the test data took %0.3f seconds&quot;%duration
        
        # execute the query
        start = time.time()
        query = db.execute(&quot;SELECT k,v FROM Testing123;&quot;)
        duration = time.time()-start
        print &quot;selecting %d rows of test data took %0.3f seconds&quot;%(query,duration)
        
        # get the rows in chunks of 10K
        rows = 0
        start = time.time()
        while query:
            batch = min(query,10*1024)
            query -= batch
            rows += len(db.fetchmany(batch))
        duration = time.time()-start
        print &quot;fetching %d batches of test data took %0.3f seconds&quot;%(rows,duration)
        
        # drop the table
        start = time.time()
        db.execute(&quot;DROP TABLE Testing123;&quot;)
        duration = time.time()-start
        print &quot;dropping the table took %0.3f seconds&quot;%duration
        
        
    test(&quot;MyISAM&quot;)
    test(&quot;InnoDB&quot;)

Why is MySQL InnoDB insert so slow?

I&#39;m currently working on creating an environment to test performance of an app; I&#39;m testing with MySQL and InnoDB to find out which can serve us best. Within this environment, we&#39;ll automatically prepare the database (load existing dumps) and instrument our test tools.

I&#39;m preparing to test the same data dump with MySQL and InnoDB, but I&#39;m already failing to bring the initial import to an usable speed for the InnoDB part. The initial dump took longer, but that didn&#39;t concerned me yet:

    $ for i in testdb_myisam testdb_innodb; do time mysqldump --extended-insert $i &gt; $i.sql; done
    
    real    0m38.152s
    user    0m8.381s
    sys     0m2.612s
    
    real    1m16.665s
    user    0m6.600s
    sys     0m2.552s

However, the import times were quite different:

    $ for i in  testdb_myisam testdb_innodb; do time mysql $i &lt; $i.sql; done
    
    real    2m52.821s
    user    0m10.505s
    sys     0m1.252s
    
    real    87m36.586s
    user    0m10.637s
    sys     0m1.208s

After research I came over https://stackoverflow.com/questions/457060/changing-tables-from-myisam-to-innodb-make-the-system-slow and then used `set global innodb_flush_log_at_trx_commit=2`:

    $ time mysql testdb_innodb &lt; testdb_innodb.sql
    
    real    64m8.348s
    user    0m10.533s
    sys     0m1.152s

IMHO still shockingly slow. I&#39;ve also disabled `log_bin` for these tests and here&#39;s a list of &lt;a href=&quot;http://my-serve.rs/tmp/mysql-variables.txt&quot;&gt;all mysql variables&lt;/a&gt;.

Do I&#39;ve to accept this long InnoDB times or can they be improved? I&#39;ve full control over this MySQL server as it&#39;s purely for this test environment.

I can apply special configurations only for initial import and change them back for applications tests so they better match production environments.

**Update:**

Given the feedback, I&#39;ve disabled autocommit and the various checks:

    $ time ( echo &quot;SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0;&quot; \
    ; cat testdb_innodb.sql ; echo &quot;COMMIT;&quot; ) | mysql testdb_innodb;date
    
    real    47m59.019s
    user    0m10.665s
    sys     0m2.896s

The speed improved, but not that much. Is my test flawed?

**Update 2:**

I was able to gain access to a different machine were imports only took about 8 minutes. I compared the configurations and applied the following settings to my MySQL installation:

    innodb_additional_mem_pool_size = 20971520
    innodb_buffer_pool_size = 536870912
    innodb_file_per_table
    innodb_log_buffer_size = 8388608
    join_buffer_size = 67104768
    max_allowed_packet = 5241856
    max_binlog_size = 1073741824
    max_heap_table_size = 41943040
    query_cache_limit = 10485760
    query_cache_size = 157286400
    read_buffer_size = 20967424
    sort_buffer_size = 67108856
    table_cache = 256
    thread_cache_size = 128
    thread_stack = 327680
    tmp_table_size = 41943040

With these settings I&#39;m now down to about 25 minutes. Still far away from the few minutes MyISAM takes, but it&#39;s getting more usable for me.

InnoDB takes over an hour to import 600MB file, MyISAM in a few minutes

I want to start using table relations in a new project.

After some googling I got 2 tables set up as InnoDB:

The keys I want to link are

-&gt;users-&gt;userid (primary)
-&gt;sessions-&gt;userid (index)

The only thing that I don&#39;t understand in this process is what the different settings for &quot;On update&quot; and &quot;On delete&quot; do

The options here are:

- -- (nothing?)
- Cascade (???)
- Set Null (sets everything to null?)
- No action (well duh...)
- Restrict (???)

I basically want the data in sessions to be deleted when a user is completely deleted
This since the sessions will only be deleted when the expiration is detected by my session manager...

So if anyone can tell me what these options do it would be much appreciated.

Setting up table relations what do &quot;Cascade&quot;, &quot;Set Null&quot; and &quot;Restrict&quot; do?

I have a simple task where I need to search a record starting with string characters and a single digit after them. What I&#39;m  trying is this

    SELECT trecord FROM `tbl` WHERE (trecord LIKE &#39;ALA[d]%&#39;)

And

    SELECT trecord FROM `tbl` WHERE (trecord LIKE &#39;ALA[0-9]%&#39;)

But both of the queries always return a `null` record 

    trecord
    -------
    null

Where as if I execute the following query

    SELECT trecord FROM `tbl` WHERE (trecord LIKE &#39;ALA%&#39;)

it returns

    trecord
    -------
    ALA0000
    ALA0001
    ALA0002

It means that I have records that starts with ALA and a digit after it,

**EDIT**

I&#39;m doing it using PHP MySQL and innodb engine to be specific.

All Innodb Solutions on Gang of Coders

Total of 42 Innodb Solutions