…is? ;-) Generative grammars like SQL can produce an infinite variety of queries. The query optimizer …gap of id’s between 47000 and 50000 with no movies, but your random number generator…columns in a compound index, then the order would matter, and it would have to match the order of columns…
Post: Implementing SchemaSpy in your MySQL environment
June 4, 2013 By 2 Comments
…this is the only component of the tool. There are additional tabs: Tables – Names, number of children, parents, count of columns, row counts, and …unique’ (woops!), single column tables, incrementing column names in tables, and tables with the string NULL instead of the actual SQL NULL value. …
Post: Understanding the maximum number of columns in a MySQL table
April 8, 2013 By 5 Comments
…=cname($_,63); $sql.=”`$n`”; $sql.=” ENUM(‘”.cname(0,64).”‘) COMMENT ‘”.cname($_,254).”‘,\n”; } chop $sql; chop $sql; $sql.=”);”; print $sql; So the maximum number of columns for a table in MySQL is somewhere between 191 and 2829, depending on a number of factors…
Post: Quick comparison of MyISAM, Infobright, and MonetDB
September 29, 2009 By 30 Comments
… with many columns (the same one Peter wrote about recently). The client’s performance is suffering in part because of the number of columns, which is over 200. The queries are generally pretty simple (sums of columns), but they’re ad-hoc (can access any columns… a 32-bit Amazon machine). The SQL implementation is impressive. It’s a really solid subset of SQL:2003, much more than I…
Post: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
August 28, 2007 By 57 Comments
…: one – to get a result set, another – to count total number of rows. In this post I’ll try to check, is… 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_CALC_FOUND_ROWS * FROM count_test…
Post: What exactly is read_rnd_buffer_size
July 24, 2007 By 4 Comments
…_size works as well as which layer it corresponds to – SQL or storage engine. Honestly as it had name very similar… TEXT/BLOB columns among them. It would be used however if there is large number of columns or there are long VARCHAR columns used – it takes only couple of UTF8 VARCHAR(255) to create…
Post: Beware of MySQL Data Truncation
February 7, 2009 By 4 Comments
… article which has insane number of comments but a as a all articles outside of allowed range have it: … you just need to enable strict mode: mysql> set sql_mode=STRICT_ALL_TABLES; Query OK, 0 rows …(4400000000,1); ERROR 1264 (22003): Out of range value adjusted for column ‘article_id’ at row 1 Note…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
… large table with a selective WHERE clause on an indexed column The SQL used for this test together with its EXPLAIN output… random index lookups needed to be performed because of the increase in the number of rows needed to be joined from supplier table… the number of rows in the table orders (left operand), and so MySQL 5.5 prefers to do a PRIMARY index scan of…
Post: Shard-Query adds parallelism to queries
November 15, 2010 By 18 Comments
… this workload. The following tests assume a workload consisting of a small number of concurrent queries (or only one) to demonstrate how much… (that is, scale better) when running small numbers of queries which examine a lot of data. What is Shard-Query? Shard-Query was… use the FlightDate column instead of the Year column. See the “full disclosure” section at the end for the complete SQL. These tests were…
Post: Knowing what pt-online-schema-change will do
November 1, 2012 By 1 Comment
… ?, 2 /*next chunk boundary*/ The SQL statements will vary highly depending on the table, the number of rows, chunking-related options, etc., but… in general the SQL statements should appear reasonable based on knowledge of the table’s structure, available indexes, total number of rows, etc. You… on never knows. Are the columns used correctly given their data types and definitions? If a column is defined as “NOT NULL…
