… a SQL query optimizer and see how easy it is? ;-) Generative grammars like SQL can produce an infinite variety of queries. The query optimizer analyzes queries for…` table first, grouping by kind_id in index order. This made the first table in the EXPLAIN seem like it was scanning more rows…
Post: The small improvements of MySQL 5.6: Duplicate Index Detection
May 31, 2013 By Leave a Comment
…); Query OK, 0 rows affected (0.25 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE… will be stricter: in the default SQL mode, a duplicate index will throw a warning instead of a note. In strict mode… detect all cases. For example: mysql> alter table test add index redundant (col2, id); Query OK, 0 rows affected (1.57 sec) Records: 0…
Post: Implementing SchemaSpy in your MySQL environment
June 4, 2013 By 2 Comments
…tables versus observing queries…Tables – Names, number of children, parents, count of columns, row counts, and comments — a great way for a high level overview of the table…tables, incrementing column names in tables, and tables with the string NULL instead of the actual SQL…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…Query OK, 0 rows affected (41 min 52.04 sec) Data dictionary Here is a quick example of the list of tables…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
…query executed basically on single table querying state by ID. Next test was a result of…id = c.state_id) ORDER BY s.name limit 10000,5\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table… rows to make query times longer. 1) Results for ENUM: select SQL…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…, to recap, every SQL query is really a …SQL doesn’t work on rows! Why would you break this paradigm by passing around rows instead of sets? From a distributed processing standpoint, rows…of 128 items * The following projections were selected for a UNIQUE CHECK * on the base table: `origin_airport_id…
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
…Query OK, 0 rows affected (0.11 sec) If we then write a SQL query to aggregate over the rows of…
Post: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
August 28, 2007 By 57 Comments
…SQL queries I pretty often see a queries with SQL_CALC_FOUND_ROWS…of all, let’s try to perform some query on this table using indexed column b in where clause: mysql> SELECT SQL_NO_CACHE SQL…
Post: Debugging problems with row based replication
May 6, 2010 By 12 Comments
…can get out of sync with the…Query OK, 1 row affected (0.02 sec) Insert a single “seed” row into the table: INSERT INTO repl.t1 VALUES (); Query OK, 1 row…SQL_Running: No Last_Errno: 1032 Last_Error: Could not execute Update_rows event on table… server id 1 end_log_pos 340637 Query thread_id=…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 14 Comments
…query completes even if table has just couple of rows. Let me however show how you can profile queries to see what exactly happens during query…
