June 19, 2013

Post: Implementing SchemaSpy in your MySQL environment

June 4, 2013 By 2 Comments

…to create this once so that the  command line is shorter and neater.  In my case here is the properties file in use for my…number of children, parents, count of columns, row counts, and comments — a great way for a high level overview of the table sizes and an easy way to…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: ,

Post: MySQL Query Patterns, Optimized - Webinar questions followup

June 3, 2013 By 2 Comments

…to make a general rule, because there are different types of subqueries (correlated, non-correlated, derived tables, scalar subqueries) and we saw in my…table to count the movies per production year for each kind_id. It was more efficient in this case to force MySQL to scan the `title` table…

Filed Under: Insight for Developers, MySQL, MySQL Webinars Tagged With: , , ,

Post: Using any general purpose computer as a special purpose SIMD computer

May 16, 2011 By 5 Comments

…to show how to count the number of unique words, an how many times those words appear in a document. Set based processing of…in set (0.00 sec) How to factor numbers create table dataset ( id bigint auto_increment primary key, prime_number…

Filed Under: MySQL

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

…how to make InnoDB FTS blazingly-fast, but simply to get a sense of how it works compared to traditional MyISAM FTS. We’ll get to performance in…of the other I_S tables, I do find that the number of rows in I_S.innodb_ft_index_table…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?

August 28, 2007 By 57 Comments

…to get a result set, another – to count total number of rows. In this post I’ll try to…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…

Filed Under: Benchmarks, Insight for Developers Tagged With:

Post: A common problem when optimizing COUNT()

September 20, 2008 By 19 Comments

…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 number of…in your query. You meant to count the number of times the column’s value is not null. This in my…

Filed Under: Insight for Developers Tagged With: ,

Post: Flexviews - part 3 - improving query performance using materialized views

April 4, 2011 By 20 Comments

…of a few seconds to incrementally refresh a view, even when the number of changes is very small. The Flexviews SQL…how you can use this summary to calculate the number of lines in the order_lines table, three orders of magnitude more quickly than COUNT(*). mysql> select count…

Filed Under: Insight for DBAs, MySQL

Post: A case for MariaDB's Hash Joins

May 31, 2012 By Leave a Comment

…to be a lot more random index lookups needed to be performed because of the increase in the number of rows needed to be joined from supplier table…SQL used for this test together with its EXPLAIN output as returned by MySQL 5.5 is as follows: SELECT o.*, count…

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , , ,

Post: Shard-Query adds parallelism to queries

November 15, 2010 By 18 Comments

…of a small number of concurrent queries (or only one) to demonstrate how much improvement could be made to…count(*)) on a range of records. This table is partitioned by month which means that MySQL can use partition pruning to reduce the amount of…

Filed Under: Insight for Developers, MySQL Tagged With: , , , ,

Post: Shard-Query EC2 images available

May 11, 2011 By 2 Comments

…number of the flight. Some data hand updated.’; — – Table structure for table `ontime_fact` — CREATE TABLE…to 20 nodes for testing. This way you can verify the numbers in my next blog post. I will compare performance of…

Filed Under: MySQL
« Older Posts