… captured with the ‘tee’ command. $ cat one.ini [default] user=mysql db=ontime_one password= port=5029 column=date_id mapper=hash gearman=127…( DISTINCT origin.CityName) from ontime_fact JOIN dim_airport dest on ( dest_airport_id = dest.airport_id) JOIN dim_airport origin on ( origin…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
… the faster one? That’s what this post is about. Let’s start with the MySQL query optimizer….all the dimension columns are indexed on every table, and there’s a separate index on every column in the WHERE … stay in memory, and this is an important distinction. Let’s put ourselves into the mindset of…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…_queue_wait: 0.000000 # InnoDB_pages_distinct: 973 SET timestamp=1325146286; select count(*) … if this query would run on a cold MySQL instance, then it would require…columns which could exclude the blob ones. Conclusion The only conclusion, I can make out is “Get yourself Percona Server, turn on…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
… you can build complete refresh views on top of incremental ones. This will allow you to … simply ignore this column. It is used to prevent wide innodb primary keys on the MV. mysql> select …COUNT_DISTINCT, a secondary view will be built in the flexviews schema, transparently, to manage the distinct …
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…on the same server, then we can simply get a snapshot of it into our schema with one command: mysql…created a view of a UNION DISTINCT of those two tables: mysql> create view used_indexes as (…(*) as COLUMN_CNT, group_concat( i.column_name order by SEQ_IN_INDEX ASC SEPARATOR ‘,’) as COLUMN_NAMES …
Post: Tools and Techniques for Index Design Webinar Questions Followup
October 4, 2012 By 5 Comments
….percona.com/catalog.php?category=17 for details on this training class and how to register to attend… relevant to current versions of MySQL. Q: How do the upcoming changes in MySQL 5.6 change the … the GROUP BY reduces it to one row per distinct value in the grouping column. Then sorting it may be …
Post: Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
… j having both of them having 1000 of distinct values, independent on each other. I also created couple of other …column index is faster for Q3 in case of low cardinality even though MySQL can’t use index well. You’re right MySQL… do full table scan or access table picking only one index on the pair.
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
… with over 30 millions of rows. “val” column in this table has 10000 distinct value, so range 1..100 selects about 1…. One of the reasons elevating this problem in MySQL is lack of advanced join methods at this point (the work is on a… sets. In MySQL single query runs as single thread (with exeption of MySQL Cluster) and MySQL issues IO requests one by one for query execution…
Post: Concatenating MyISAM files
November 19, 2012 By 5 Comments
… identical MyISAM files for distinct parts of the …almost impossible) to locate on a drive with tools… back in mysql: mysql> use test Database changed mysql> flush tables;…one | | 2 | two | | 3 | three | | 4 | four | | 5 | five | | 6 | six | +—-+——-+ 6 rows in set (0.00 sec) So varchar columns…
Post: Using Flexviews - part one, introduction to materialized views
March 23, 2011 By 3 Comments
… I’m giving a talk on it at the MySQL 2011 CE, and …really a virtual table. It has columns and rows, just like a database…expensive. Views containing grouping, aggregation, sorting, distinct or other conditions must be … all be transactionally consistent with one another, unless you stop all database…
