…: Tables – Names, number of children, parents, count of columns, row counts, and comments — a great way for a high level overview of the table…’ (woops!), single column tables, incrementing column names in tables, and tables with the string NULL instead of the actual SQL NULL value. This is…
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
… there are different types of subqueries (correlated, non-correlated, derived tables, scalar subqueries) and we saw in my presentation that sometimes… optimizer and see how easy it is? ;-) Generative grammars like SQL can produce an infinite variety of queries. The query optimizer…. But the result was that it created a temporary table to count the movies per production year for each kind_id. It…
Post: Understanding the maximum number of columns in a MySQL table
April 8, 2013 By 5 Comments
…length(table_options),reclength); if (info_length+(ulong) create_fields.elements*FCOMP+288+ n_length+int_length+com_length > 65535L || int_count…cname($_); $sql.=”`$n`”; $sql.=” ENUM(‘Y’,'N’,'M’,’0′,’1′,’2′)\n”; } chop $sql; chop $sql; $sql.=”);”; print $sql; This gets you a 46kb CREATE TABLE …
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…table_definition_cacheblogpercona.commanual table_lock_wait_timeoutblogpercona.commanual Table_locks_immediateblogpercona.commanual Table_locks_waitedblogpercona.commanual table_open_cacheblogpercona.commanual table…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…the first argument on the command line: $ cat sales.sql create table dashboard_customer_sales AS select customer_id, customer_name, DATE…of lines in the order_lines table, three orders of magnitude more quickly than COUNT(*). mysql> select count(*) cnt from order_lines\G *************************** 1…
Post: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
August 28, 2007 By 57 Comments
… query on this table using indexed column b in where clause: mysql> SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS * FROM count_test WHERE….00 sec) mysql> explain SELECT SQL_NO_CACHE count(*) FROM count_test WHERE b = 666; +—-+————-+————+——+—————+——+———+——-+——+————-+ | id | select_type | table | type | possible_keys | key | key…
Post: Best kept MySQLDump Secret
March 23, 2012 By 13 Comments
…tables; +——————–+ | Tables_in_dumptest | +——————–+ | A | | B | | C | +——————–+ 3 rows in set (0.00 sec) mysql> select count(*) from A; +———-+ | count…
Comment: Four ways to optimize paginated displays
October 3, 2008 By 27 Comments
… have recently added paging to my website. First attempt used SQL_COUNT_FOUND_ROWS everywhere. This, unfortunately, slowed things down considerably. My… forum: ———————————————– 1) I have a statistics table that is updated by triggers on inserts: statistics_table(serviceid, count, lastinsert): +——–+———+—————-+ | SERVICE| COUNTER | LASTINSERT | |——–|———|—————-| | forum… user etc. then I fall back to the SQL_COUNT_FOUND_ROWS method. The tables are so small that they are always cached…
Post: Analyzing Slow Query Table in MySQL 5.6
February 18, 2013 By 1 Comment
… query information to the log table, query times and lock times are truncated to to integers: sql/log.cc, Log_to_csv… log tables are rounded down to the nearest whole second, meaning any query that executes in less than 1.0 seconds counts…,Reset stmt,Set option,Fetch,Daemon,Error’), CONCAT(‘# administrator command: ‘, sql_text), sql_text), ‘;’ ) AS `# slow-log` FROM `mysql`.`slow_log`; GO…
Post: A common problem when optimizing COUNT()
September 20, 2008 By 19 Comments
… COUNT() contains a column name, like this: select count(col1) from table; If you know your SQL well, you know COUNT() has two meanings. 1) count the number of rows 2) count the…’s another form for COUNT(): select count(distinct col1) from table; So when I see a query that just does COUNT(col1) I am…
