Hot answers tagged

3

No. If your table has the InnoDB engine and the PRIMARY KEY is (pet_id), then defining a secondary index as (person_id) or (person_id, pet_id) makes no difference. The index includes the pet_id column as well so values are sorted as (person_id, pet_id) in both cases. A query like the one you have: SELECT pet_id FROM yourtable WHERE person_id = 127 ORDER ...


2

Tip 1: PRIMARY KEY(x, id), INDEX(id) -- where `id` is `AUTO_INCREMENT` is perfectly valid. It has the performance advantage of being more efficient when many queries need to find multiple rows WHERE x = 123. That is, it is slightly more efficient than the 'obvious' PRIMARY KEY(id), INDEX(x, id) The only rule about AUTO_INCREMENT (for InnoDB) is that ...


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

For a table with 1000 rows I think it does not make sense to put any index. The complete table could fit into memory and that would be fast enough. But if you want to put indexes you should always take the business value into consideration. Take into account that each index generates an overhead to the table. Not sure where the limit is, but in any case ...


2

Don't randomly add indexes. Look at your queries to decide which indexes are needed. See my cookbook. InnoDB really needs a PRIMARY KEY. Keep in mind that a PK is, an index, is UNIQUE, and is clustered. So don't add any index(es) that start with the same column(s). WHERE a=2 AND b=4 begs for a "composite" index: INDEX(a,b) or INDEX(b,a). Those are ...


2

Just going by the MySQL Documentation, I would say yes. According to the MySQL 5.6 Documentation on innodb_adaptive_hash_index Disabling the adaptive hash index empties the hash table immediately. Normal operations can continue while the hash table is emptied, and executing queries that were using the hash table access the index B-trees directly ...


2

According to the MySQL Documentation on the Clustered and Secondary Indexes How Secondary Indexes Relate to the Clustered Index All indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. ...


1

Under MySQL 5.7, ROW_FORMAT=FIXED is no longer an option for the InnoDB Storage Engine. According to the MySQL 5.7 Documentation for innodb_default_row_format, these are values you can use for the table's row format: DYNAMIC, COMPACT, and REDUNDANT are allowed as default values COMPRESSED is not allowed to a default value, but you can explicitly use ...


1

(This does not address your question directly; rather it addresses your goal of deleting old records.) Plan A You are talking about DELETEing "old" records. Assuming you are removing "all" the old records, then PARTITIONing is an excellent way to do that. PARTITION BY RANGE(TO_DAYS) by weeks. Use DROP PARTITION (instantaneous and non-invasive) to get ...


1

What you did applies to tables one at a time. Instead... Plan A: Change datadir when installing. (See documentation for details and caveats.) Plan B: Symlink .../mysql/data to some place in the other partition. (And remove DATA_DIRECTORY, etc from CREATE TABLE.)


1

The short answer is no, MySQL does not have that 'feature'. The long answer (and opinion)... Perhaps 1% of tables are partitioned. Perhaps 1% of partitioned tables could use that feature. Now imagine the effort to implement, test, and deploy such an obscure feature. Oracle has had several decades to incorporate little-used features like that. MySQL is ...


1

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


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


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

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

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

Found a solution that works MariaDB [(none)]> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_DATAFILES WHERE PATH LIKE '%5377%'; +---------+---------------------------------+ | SPACE | PATH | +---------+---------------------------------+ | 2432544 | ./db5377/menu_router.ibd | +---------+---------------------------------+ 1 row ...


1

It could be related to the value of foreign_key_checks variable. Try the following statement, and check the value: mysql> show variables like 'foreign_key_checks'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | foreign_key_checks | ON | +--------------------+-------+



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