On Friday I gave a presentation on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars. If you missed it, you can still… doesn’t matter. However, if I were using the tuple syntax to look up values against multiple columns in a compound… more efficient in this case to force MySQL to scan the `title` table first, grouping by kind_id in index order. This…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… constantly referring to the amazing MySQL manual, especially the option and variable reference table. But…commanual defaults_extra_fileblogpercona.commanual defaults_fileblogpercona.commanual defaults_group_suffixblogpercona.commanual delay_key_writeblogpercona.commanual Delayed_errorsblogpercona….
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
… queries to use ANSI JOIN syntax. No other changes to the queries were made. Test Environment The MySQL versions used at the…_schema.innodb_buffer_page_lru where table_name like ‘%ssb%’ group by 1,2 ) sq order by pct_buffer_pool desc… modified versions of the queries (just to use ANSI JOIN syntax): — Q1.1 select sum(lo_extendedprice*lo_discount) as revenue…
Post: Should MySQL Extend GROUP BY Syntax ?
August 17, 2007 By 11 Comments
… in the group: This is one illustration of group by limitations in SQL language which is not offset by any MySQL specific extensions,yet As you can see if you want to get one row from the group which is… columns, something like SELECT MAX(Population), City, Country FROM City GROUP BY Country GROUPORDER BY Population DESC; This could give you…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… giving me information about LVM devices, volume groups, and logical volumes. At this point, unless …mysql –user=mysql –pid-file=/var/run/mysqld/mysqld.pid –skip-external-locking –port=3306 –socket=/var/db/mysql/mysql… strange that might be caused by a syntax error in the my.cnf file. For an…
Post: Air traffic queries in InfiniDB: early alpha
November 2, 2009 By 19 Comments
…Calpont InfiniDB Syntax guide for additional information on supported distributed syntax or consider changing the InfiniDB Operating Mode (infinidb_vtable_mode). mysql> show… DISTINCT OriginCityName) FROM ontime WHERE YearD BETWEEN 2008 and 2008 GROUP BY DestCityName ORDER BY 2 DESC LIMIT 10; And …
Comment: A common problem when optimizing COUNT()
September 21, 2008 By 19 Comments
…, character varying) does not exist … I would imagine that the syntax I showed ( using a derived table) would work on most… to many MySQL users not understanding what is going on when they type GROUP BY. I think that ONLY_FULL_GROUP_BY was a checkbox that was checked so that MySQL might allow…
Post: Using Flexviews - part one, introduction to materialized views
March 23, 2011 By 3 Comments
…is the output of a SQL statement. MySQL even includes a SQL statement that … the computation is likely expensive. Views containing grouping, aggregation, sorting, distinct or other conditions must…work. And some drawbacks: Not all SQL syntax supported (no outer join), no non-deterministic functions…
Post: How to start a Percona XtraDB Cluster
January 29, 2013 By 10 Comments
… time, you can use wsrep_cluster_address with the following syntax: wsrep_cluster_address=gcomm://192.168.1.1,192.168… error: 130129 17:03:45 [Note] WSREP: gcomm: connecting to group ‘testPXC’, peer ’192.168.80.1:,192.168.80.2… again, this is not my favourite option. start mysql using the following syntax (it works only on RedHat and CentOS out of…
Comment: Database problems in MySQL/PHP Applications
November 6, 2006 By 13 Comments
… CHANGE MASTER TO command (fully described in our manual) – # the syntax is: # # CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=, # MASTER_USER… = /usr/local/mysql/data/ #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend #innodb_log_group_home_dir = /usr/local/mysql/data/ #innodb_log_arch_dir = /usr/local/mysql/data/ innodb_data_home…
