…, MySQL always uses Nested Loop Join to join two or more tables. What this means is that, select rows from first table… as probe step. This join algorithm works best for highly selective queries, and obviously when the first operand used in the… read more about these variables here. The query used is: select l_orderkey, sum(l_extendedprice * (1 – l_discount)) as revenue…
Comment: Mass killing of MySQL Connections
May 21, 2009 By 25 Comments
… select_cursor; cursor_loop: LOOP FETCH select_cursor INTO query_id; IF iteration_complete THEN LEAVE cursor_loop; END IF; KILL QUERY query_id; END LOOP; CLOSE select_cursor…
Post: kernel_mutex problem. Or double throughput with single variable
December 2, 2011 By 17 Comments
… InnoDB starts the transaction, usually there is loop through ALL active transactions, and this loop is inside kernel_mutex. That is to… transactions. For this we will take sysbench running only simple select PK queries against 48 tables, 5,000,000 rows each… complex handling with spin loops, and there are two variables that affects mutex loops: innodb_sync_spin_loops and innodb_spin_wait_delay…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 139 Comments
… remember if indexes are helpful or not depends on index selectivity – how large proportion of rows matches to particular index value… for full table scan vs range scan by index: mysql> select count(pad) from large; +————+ | count(pad) | +————+ | 31457280 | +————+ 1 row in… to large tables Joining of large data sets using nested loops is very expensive. Try to avoid it. Joins to smaller…
Comment: Getting around optimizer limitations with an IN() list
February 21, 2010 By 9 Comments
Spatial search = Postgres. SELECT count(*) FROM archive_data; count ——— 3063044 EXPLAIN ANALYZE SELECT count(*) FROM archive_data WHERE coords && ‘(45.64870078164,4… width=0) (actual time=3.197..5.828 rows=11686 loops=1) Recheck Cond: (coords && ‘(45.68463321836,4.8423759421357),(45.64870078164…
Post: How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
… had locks and undo log records, yet had only run SELECT queries since its inception. That case is still open and I am still stumped as to how a SELECT-only transaction can possibly have undo log records! The greater…, small transactions. Don’t design batch jobs that run in loops and update data, unless you make them commit between iterations…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
…: 30 Extra: Using where; Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: info type: range possible_keys: d key… we get the range clause on “info” table instead nested loops join – exactly what we tried to avoid. It is easy…: 30 Extra: Using where; Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: g type: index possible_keys: PRIMARY key…
Post: GROUP_CONCAT useful GROUP BY extension
September 4, 2006 By 43 Comments
… can be useful? For example to get PHP array without looping inside PHP: Table: CREATE TABLE services ( id INT UNSIGNED NOT… = 3; +—-+———–+ | id | client_id | +—-+———–+ | 3 | 5 | | 3 | 6 | | 3 | 7 | +—-+———–+ SELECT id,GROUP_CONCAT(client_id) FROM services WHERE id = 3…
Post: PBXT benchmarks
April 8, 2007 By 14 Comments
….com/files/benchpbxt/mysql.start and script to run whole loop of benchmarks http://www.mysqlperformanceblog.com/files/benchpbxt/go.run…`,`state_id`,`city`) ) In this benchmark we used only read (SELECT) queries with different typical data access patterns: primary key single… comments READ_PK_POINT Query: SELECT name FROM $tableName WHERE id = %d READ_KEY_POINT Query: SELECT name FROM $tableName WHERE country…
Post: mk-query-digest, query comments and the query cache
… then be cross-referenced between MySQL logs and Apache logs. select 1 — File: myfile.php Line: 25 Function: myFunc SessionID: a1b2c3d4e5f6a7b8c9d0aef… a simple test script to demonstrate the difference. The test loops twice over a set of query patterns. Each pattern is… with # and — comments at the start of a query(before SELECT), and in some cases, with varying white-space. These issues…