… doing up to 6 table joins, 1 insert and 1 delete. CMT servers are a perfect fit for multi-instance MySQL environment. And…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… max_error_countblogpercona.commanual max_heap_table_sizeblogpercona.commanual max_insert_delayed_threadsblogpercona.commanual max_join_sizeblogpercona.commanual max_length_for_sort…_zoneblogpercona.commanual table_definition_cacheblogpercona.commanual table_lock_wait_timeoutblogpercona.commanual Table_locks_immediateblogpercona.commanual Table_locks_waitedblogpercona.commanual table_open_cacheblogpercona.commanual table_typeblogpercona.commanual…
Post: Scaling problems still exist in MySQL 5.5 and Percona Server 5.5
May 20, 2011 By 11 Comments
… values distribution. The query looks something like this: SELECT * FROM table WHERE deleted = 0 AND group_id IN (62715996, 62716592, 62717660…_range_first,handler::read_multi_range_next,QUICK_RANGE_SELECT::get _next,rr_quick,sub_select,do_select,JOIN::exec,mysql_select…_fetch,handler::read _multi_range_next,QUICK_RANGE_SELECT::get_next,rr_quick,sub_select,do_select,JOIN::exec,mysql_select,handle…
Post: Data mart or data warehouse?
July 15, 2010 By 14 Comments
… three table item/category/item_category tables in the warehouse schema example would be considered a snowflake. A dimension table (item) must be joined to additional tables (item… I’ll talk more about Mondrian and about MDX, the multi-dimensional query language. Mondrian turns MDX into SQL, so we…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… own clients, with no delays. Finally, the analysis is usually multi-dimensional. The typical user wants to be able to… big joins; the query optimizer can sometimes pick bad plans when you have a lot of joins between fact and dimension tables, and… a custom table, which is really constructed behind the scenes from the name-value tables in a complex query with many joins. I…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 8 Comments
… like a single-row or multi-row INSERT/REPLACE uses a light-weight mutex instead of a table lock on AUTO-INC… rid of the gaps problem. A “mutex” table is a clever trick that allows joining tables while keeping them independent of each other… check the auto_increment counter: show create table foo\G *************************** 1. row *************************** Table: foo Create Table: CREATE TABLE `foo` ( `id` int(11) NOT NULL AUTO…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
… the usage and performance of the splitter. It is multi-threaded(actually multi-process) and is able to hash split up to… number of the flight. Some data hand updated.’; — – Table structure for table `ontime_fact` — CREATE TABLE `ontime_fact` ( `date_id` int(11) NOT…-thread-sleep-delay=0 innodb-use-sys-stats-table innodb-write-io-threads=4 join-buffer-size=16M key-buffer-size=64M…
Comment: MySQL Server Memory Usage
September 1, 2006 By 21 Comments
…tables‘, ’102′ ‘Com_update’, ’15146253′ ‘Com_update_multi‘, ’0′ ‘Connections’, ’263413′ ‘Created_tmp_disk_tables‘, ’0′ ‘Created_tmp_files’, ’4001′ ‘Created_tmp_tables…
Comment: Why MySQL could be slow with large tables ?
July 6, 2007 By 141 Comments
… MSSQL and multi-GB tables (up to 1 TB) with some very ridiculous normalisation and absurd number of columns (some tables have > 900… things faster. (At least I don’t have to join so many tables to get the data) The other thing which I… total I have like close to 11 subqueries joining a min of 4 tables at each instance) Ps : reading Eric/k statement…
Post: Using Flexviews - part one, introduction to materialized views
March 23, 2011 By 3 Comments
… so, then keep reading. This is the first in a multi-part series describing Flexviews. Update: This series has a part…, accessing the table will be considerably cheaper You can add indexes to the MV. Since the data is already joined together and… twice as much space (similar to ALTER TABLE) Supports all SQL syntax (like outer join) but can’t be refreshed to a…
