… with data. My data will fit in around 10 tables of 90Gb each one, After it is filled I only need to… can I fill this information fast. I used one primary key with three index, and one index separated, this because after it is filled in… is very slow. I am also thinking in partition the table, but I read in other blocks that it might not…
Post: PBXT benchmarks
April 8, 2007 By 14 Comments
… access patterns: primary key single row lookup, primary key range lookup, same access types for primary key and full table scans…. result we get a maximal result of three runs. The raw numbers are available here:… results. The one problem comparing to InnoDB is the access to data by Primary KEY, as I…
Post: Drop table performance
April 20, 2011 By 22 Comments
…one we have in our lab: a Cisco UCS C250 with 340GB of RAM. To fix this problem, we implemented “background table… “CREATE TABLE ts_$i (begin_time int(11) NOT NULL, end_time int(11) NOT NULL, PRIMARY KEY (begin… number of dropped tables per second (more is better): As we see, all three versions are …
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… data; try to make your rows fixed-length. One reason denormalization is important is that nested-loop …there are indexes other than the primary key, we can shrink the primary key‘s width: create table ads_by_day_by_blueness … get rid of it. I wrote a three-part article on data archiving on my own …
Post: Joining on range? Wrong!
May 17, 2010 By 10 Comments
… at phpDay.it conference last week to demonstrate some pitfalls one may hit when designing schemas and queries, but then I… of three very simple tables: CREATE TABLE `products` ( `prd_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `prd_name` varchar(32) NOT NULL, PRIMARY KEY… this? *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t type: ref possible_keys: PRIMARY key: PRIMARY key_len: 98 ref: const rows: 1 Extra: Using…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 3 Comments
…with some subdirectories inside, one for every single index in the table: ~/recovery-tool# ls …Primary Key There are different methods to find the correct index and here I’m going to explain three… is our primary key. After identifying the Primary Key don’t forget to remove the innodb_table_monitor. C…
Post: Using Flexviews - part two, change data capture
March 25, 2011 By 11 Comments
… database table, or tables for changes. Improve ETL performance by identifying the data which has changed. Maintain materialized views with Flexviews (the primary… insert some more of them: mysql> create table > test.demo ( > c1 int auto_increment primary key, > c2 int > ) > engine=innodb; Query OK, 0… are three rows in the changelog, each representing one of the changes we made. You will notice that the source table only has…
Post: EXPLAIN EXTENDED can tell you all kinds of interesting things
June 15, 2010 By 7 Comments
… three empty tables. It is important to note that the tables are empty because the MySQL optimizer treats empty tables (and incidentally, tables with only one…’ tables. A ‘const’ table is a table that contains 0 or 1 rows, or a table on which all parts of a primary key or unique key…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
… views from SQL statements. It reads one or more “CREATE TABLE db.schema … AS SELECT” and/or…column. It is used to prevent wide innodb primary keys on the MV. mysql> select mview$pk as…the number of lines in the order_lines table, three orders of magnitude more quickly than COUNT(*). mysql…
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 6 Comments
… and the single threaded operation burns one CPU. To help completely demonstrate how …, 4.9994130134583s, 0.0063340663909912s) The set of three numbers are wall clock time (as calculated…) How to factor numbers create table dataset ( id bigint auto_increment primary key, prime_number bigint ); …
