Hot answers tagged

2

We have millions of tables deployed running ARIA in production. It is definitely a better choice over MyISAM as far as crashing and recovering from a crashed table. If you have high concurrent write neither are a option as they both only support table level locks. For tables with more write intensive operations stick with InnoDB, and with heavy write tables ...


2

The InnoDB/XtraDB is a table engine, it has no say in parsing SQL. Thats another layer, which should be mostly engine independent. MariaDB 5.5 is supposed to be 99.99% SQL compatible with MySQL 5.5 (there were some minor hiccups) and MariaDB 10.x is backward compatible with 5.5. With MariaDB 10 and MySQL 5.6 (and now 5.7) the paths diverged somehow - some ...


2

Not matter what you configure innodb_file_per_table to be (0 or 1), the following still occurs MySQL creates a database folder MySQL creates every table's .frm in a database folder EXAMPLE Your database directory (datadir) is /var/lib/mysql You run the following commands CREATE DATABASE IF NOT EXISTS mydb; USE mydb CREATE TABLE tb1 (a INT NOT NULL) ...


1

The information_schema database is an all memory database made up of temporary tables. It is not stored in any of the InnoDB plumbing. I wrote about this 5 years ago : How is INFORMATION_SCHEMA implemented in MySQL? If you performed a mysqldump of the infomration_schema, reimporting it will do nothing. mysqld will correct and protect any outside manual ...


1

The second option should be the fastest. It was made for this. Also it should have no bugs since it is used a lot and already for a long time. If you go for this then you do not even need to use a composite primary key. In my opinion the only reason to use the first option is if you need a numbering starting from 1 per client.


1

My understanding: The table contains 1M rows of which 250k are returned by the query. There are 500k rows with foreign_key_id = 1 and 500k rows with af.foreign_key_id2 IS NOT NULL. The query using full table scan (actually doing full index scan on the PRIMARY key in InnoDB) will read all 1M rows sequentially and check each of them for the conditions. The ...


1

EXPLAIN tells you the difference. If it shows DEPENDENT SUBQUERY it means that the subquery in IN() is executed once-per-row for the table1, that may be really many times if the table is big. Different MySQL versions may apply different optimizations to the first query to get rid of the DEPENDENT SUBQUERY or at least minimize its performance impact: use ...


1

You just asked Does: INSERT INTO table1... SELECT .. FROM table2 Also create a lock on table2? Yes, it does create a lock on table2. I wrote about this behavior back on Aug 08, 2014 (See my answer to MySQL consistent nonlocking reads vs. INSERT ... SELECT) In my old post, I mentioned from the MySQL Documentation: By default, InnoDB uses ...


1

I'll disagree with the assessment that "you are lucky enough to have a buffer pool with a perfect 100% hit rate" At the top of the output (which is chopped off), is a line something like: Per second averages calculated from the last 16 seconds This says to me that no reads happened in the last 16 seconds, thereby (artificially) giving you a perfect ...



Only top voted, non community-wiki answers of a minimum length are eligible