…: Doesn’t the primary key solution for random selection only work when the IDs for movies are… from what you specify in the query? The tuple comparison you’re referring to is this example: WHERE … first table in the EXPLAIN seem like it was scanning more rows. But by avoiding the temporary table, and…
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
… is a table identifier. We will need the table id to find indexes of the table. mysql> select * from SYS_TABLES WHERE NAME=’sakila/actor’; … for. It sounds like a short from “precision”, but at least for DECIMAL type where it would make …
Comment: MySQL Partitioning - can save you or kill you
April 25, 2013 By 45 Comments
Hello Peter, If i have a table with 20,000,000 rows around 50GB and if i made just simple querys like: select * from table where id = ’1549090′ Creating a partition by key(id) (30 partitions) will increase my performance for selects and inserts? Thanks
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… queries: select avg(id) from sbtest$i force key (primary) select count(*) from sbtest$i WHERE k like ‘%0%’ SysBench-0.5/lua: POINT_SELECT QPS test sysbench –num-threads=<1..1024> –test=oltp.lua –oltp_tables…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
… use two different schemas. The first schema is from sysbench, and the table looks like: CREATE TABLE sbtest$I ( id BIGINT UNSIGNED NOT NULL… workload (which actually is closer to what I need). Tables looks like: CREATE TABLE `sbtest1` ( `hid` int(10) unsigned NOT NULL DEFAULT ’0…`) where `id` is sequential, and `hid`,`mid` is low selectivity is not good for fast inserts, but it is suitable for range selects…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 10 Comments
… can look something like this: mysql> explain select * from idxitest where a=100 order by id desc limit 1; +—-+————-+———-+——+—————+——+———+——-+——+——————————————+ | id | select_type | table | type | possible_keys…
Post: When the subselect runs faster
March 18, 2010 By 9 Comments
…, we had a query optimization request from one of our customer. The query was very simple like: SELECT * FROM `table` WHERE (col1=’A'||col1=’B') ORDER BY id DESC LIMIT 20 OFFSET 0 This column in the table is looks like this…
Post: Quickly preloading Innodb tables in the buffer pool
May 1, 2008 By 9 Comments
… you can load Innodb Table Clustered Index in the buffer pool pretty efficiently by using something like SELECT count(*) FROM tbl WHERE non_index_col=0 This works relatively well (though can be slow for fragmented tables) but it…
Post: How expensive is a WHERE clause in MySQL?
October 31, 2008 By 17 Comments
… TABLE `t` ( `a` date NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; insert into t(a) values(current_date); insert into t select * from…’s see how long a table scan with no WHERE clause takes: mysql> select sql_no_cache count(*) from t; +———-+ | count(*) | +———-+ | 8388608 | +———-+ 1 row… overhead of the WHERE clause is about 2.95 seconds, or 351 nanoseconds per row. It doesn’t seem like much per…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 4 Comments
… in /root/recovery-tool folder and the data like tablespaces and recovered rows in /root/recovery-tool/data…also get the table row format from the Information Schema: mysql (information_schema) > SELECT ROW_FORMAT from TABLES WHERE TABLE_SCHEMA=’employees’ AND TABLE_NAME=’salaries’; +————+ …
