… other drawbacks to using temporary tables in this way, for example when using replication, a… in a suboptimal form. Q: Doesn’t the primary key solution for random selection…in a compound index, then the order would matter, and it would have to match the order of columns in the index. Example: WHERE…
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
… know how InnoDB uses `PREC` please let me know. mysql> select * from SYS_COLUMNS WHERE TABLE_ID=741; …their content. Index id in our example is 1679: mysql> SELECT * FROM SYS_FIELDS WHERE INDEX_ID = 1679; +———-+—–+———-+ | INDEX_ID | POS…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
…-only transaction optimization in MySQL 5.6, because all SELECT queries in the AUTOCOMMIT mode are,…use following queries: select avg(id) from sbtest$i force key (primary) select count(*) from sbtest$i WHERE…-only=off –oltp-index-updates=0 –oltp-non-index-updates=0 run POINT_SELECT + UPDATE QPS test…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…-up aggregation, which should produce updates in-place (I will use INSERT .. ON DUPLICATE KEY …) DEFAULT ” NOT NULL, PRIMARY KEY (id) ) CREATE INDEX k on sbtest$I(k) and …where `id` is sequential, and `hid`,`mid` is low selectivity is not good for fast inserts, but it is suitable for range selects…
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 8 Comments
… rows: 296338 Extra: Using where; Using filesort 1 row in set (0.00 sec) mysql> explain select * from goods force index(cat_id) where cat_id=5… Extra: Using where 1 row in set (0.00 sec) As you can see if given no hint MySQL will prefer to use index on… is badly selective using index scan is much better idea. Until MySQL is able to handle this you will have to use force index hint…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 28 Comments
… and where clauses on the table. In such case you would see “Index” type in explain which correspond to scanning (potentially) complete table in the index…, B DESC will be able to use full index for sorting (note MySQL may not select to use index for sort if you sort full… you will see “using index” in EXPLAIN output. These are the main “core” use for indexes. You can also see others like using index for group by…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
…Using intersect(i2,i1); Using where | +—-+————-+———+————-+—————-+——-+———+——+——+————————————-+ 1 row in set (0.00 sec) Hm… Optimizer decides to use index merge in…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… query like so, with the following result: SELECT user_id FROM users USE INDEX(user_type) WHERE user_type=2 AND user_id > 2938575… developers might see USE INDEX() and suddenly start believing that they can always outsmart the optimizer by manually manipulating indexes in every query they…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 10 Comments
…select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +—-+————-+———-+——+—————+——+———+——-+——+——————————————+ | 1 | SIMPLE | idxitest | ref | a | a | 4 | const | 1 | Using where; Using index; Using…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
… small table that fits in memory to a large table with a selective WHERE clause on an indexed column The SQL used for this test… | Using where; Using index; Using temporary; Using filesort | | 1 | SIMPLE | lineitem | ref | i_l_suppkey | i_l_suppkey | 5 | dbt3.supplier.s_suppkey | 292 | 100.00 | Using where…
