You searched for mysql distinct count index - Page 2 of 3

InnoDB: look after fragmentation

November 5, 2009 By Vadim Tkachenko 12 Comments

One problem made me puzzled for couple hours, but it was really interesting to figure out what’s going on. So let me introduce problem at first. The table is

CREATE TABLE `c` (
  `tracker_id` int(10) unsigned NOT NULL,
  `username` char(20) character set latin1 collate latin1_bin NOT NULL,
  `time_id` date NOT NULL,
  `block_id` int(10) unsigned default NULL,
  PRIMARY KEY  (`tracker_id`,`username`,`time_id`),
  KEY `block_id` (`block_id`)
) ENGINE=InnoDB

CREATE TABLE `c` (

`tracker_id` int(10) unsigned NOT NULL,

`username` char(20) character set latin1 collate latin1_bin NOT NULL,

`time_id` date NOT NULL,

`block_id` int(10) unsigned default NULL,

PRIMARY KEY (`tracker_id`,`username`,`time_id`),

KEY `block_id` (`block_id`)

) ENGINE=InnoDB

Table has 11864696 rows and takes Data_length: 698,351,616 bytes on disk The problem is that after restoring table from mysqldump, the query that scans data [...]

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

Air traffic queries in InfiniDB: early alpha

November 2, 2009 By Vadim Tkachenko 19 Comments

As Calpont announced availability of InfiniDB I surely couldn’t miss a chance to compare it with previously tested databases in the same environment. See my previous posts on this topic: Analyzing air traffic performance with InfoBright and MonetDB Air traffic queries in LucidDB I could not run all queries against InfiniDB and I met some [...]

Filed Under: Benchmarks, MySQL Tagged With: Data Warehousing, OLAP

How (not) to find unused indexes

October 16, 2009 By Morgan Tocker 2 Comments

I’ve seen a few people link to an INFORMATION_SCHEMA query to be able to find any indexes that have low cardinality, in an effort to find out what indexes should be removed.Â This method is flawed – here’s the first reason why:

CREATE TABLE `sales` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`customer_id` int(11) DEFAULT NULL,
`status` enum('archived','active') DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `status` (`status`)
) ENGINE=MyISAM AUTO_INCREMENT=65691 DEFAULT CHARSET=latin1;
mysql> SELECT count(*), status FROM sales GROUP by status;
+----------+---------+
| count(*) | statusÂ  |
+----------+---------+
|Â Â Â  65536 | archived |
|Â Â Â Â Â  154 | activeÂ  |
+----------+---------+
2 rows in set (0.17 sec)
mysql> EXPLAIN SELECT * FROM sales WHERE status='active'; # query 1
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | keyÂ Â Â  | key_len | refÂ Â  | rows | ExtraÂ Â Â Â Â Â  |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
|Â  1 | SIMPLEÂ Â Â Â Â  | sales | refÂ  | statusÂ Â Â Â Â Â Â  | status | 2Â Â Â Â Â Â  | const |Â  196 | Using where |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
1 row in set (0.06 sec)
mysql> EXPLAIN SELECT * FROM sales WHERE status='archived'; # query 2
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | keyÂ  | key_len | refÂ  | rowsÂ  | ExtraÂ Â Â Â Â Â  |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|Â  1 | SIMPLEÂ Â Â Â Â  | sales | ALLÂ  | statusÂ Â Â Â Â Â Â  | NULL | NULLÂ Â Â  | NULL | 65690 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.01 sec)

CREATE TABLE `sales` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`customer_id` int(11) DEFAULT NULL,

`status` enum('archived','active') DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `status` (`status`)

) ENGINE=MyISAM AUTO_INCREMENT=65691 DEFAULT CHARSET=latin1;

mysql> SELECT count(*), status FROM sales GROUP by status;

+----------+---------+

| count(*) | statusÂ |

+----------+---------+

|Â Â Â 65536 | archived |

|Â Â Â Â Â 154 | activeÂ |

+----------+---------+

2 rows in set (0.17 sec)

mysql> EXPLAIN SELECT * FROM sales WHERE status='active'; # query 1

+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+

+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+

+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+

1 row in set (0.06 sec)

mysql> EXPLAIN SELECT * FROM sales WHERE status='archived'; # query 2

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

1 row in set (0.01 sec)

The cardinality of status index is woeful, but provided that the application [...]

Filed Under: Insight for DBAs, MySQL Tagged With: explain, indexing, MySQL, Tips

Analyzing air traffic performance with InfoBright and MonetDB

October 2, 2009 By Vadim Tkachenko 50 Comments

Accidentally me and Baron played with InfoBright (see http://www.mysqlperformanceblog.com/2009/09/29/quick-comparison-of-myisam-infobright-and-monetdb/) this week. And following Baron’s example I also run the same load against MonetDB. Reading comments to Baron’s post I tied to load the same data to LucidDB, but I was not successful in this. I tried to analyze a bigger dataset and I took public [...]

Filed Under: Benchmarks, MySQL Tagged With: Infobright, MonetDB, OLAP, reporing

Why InnoDB index cardinality varies strangely

September 28, 2009 By Baron Schwartz 5 Comments

This is a very old draft, from early 2007 in fact. At that time I started to look into something interesting with the index cardinality statistics reported by InnoDB tables. The cardinality varies because it’s derived from estimates, and I know a decent amount about that. The interesting thing I wanted to look into was [...]

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

A micro-benchmark of stored routines in MySQL

August 16, 2009 By Baron Schwartz 7 Comments

Ever wondered how fast stored routines are in MySQL? I just ran a quick micro-benchmark to compare the speed of a stored function against a “roughly equivalent” subquery. The idea — and there may be shortcomings that are poisoning the results here, your comments welcome — is to see how fast the SQL procedure code [...]

Filed Under: Benchmarks Tagged With: Optimizer, stored functions, stored procedures, stored routines

Just do the math!

July 6, 2009 By Peter Zaitsev 4 Comments

One of the most typical reasons for performance and scalability problems I encounter is simply failing to do the math. And these are typically bad one because it often leads to implementing architectures which are not up for job they are intended to solve. Let me start with example to make it clear. Lets say [...]

Filed Under: Insight for Developers Tagged With: Tips

Dropping unused indexes

January 15, 2009 By Peter Zaitsev 6 Comments

Vadim wrote some time ago about how to find unused indexes with single query. I was working on the system today and found hundreds of unused indexes on dozens of tables so just dropping indexes manually did not look fun. So I extended Vadim’s query to generate ALTER TABLE statements automatically. I also made it [...]

Filed Under: Insight for DBAs Tagged With: Tips

ANALYZE: MyISAM vs Innodb

September 3, 2008 By Peter Zaitsev 9 Comments

Following up on my Previous Post I decided to do little test to see how accurate stats we can get for for Index Stats created by ANALYZE TABLE for MyISAM and Innodb. But before we go into that I wanted to highlight about using ANALYZE TABLE in production as some people seems to be thinking [...]

Filed Under: Insight for DBAs Tagged With: InnoDB, MyISAM

The new cool MySQL patch has landed! Check your queries performance!

October 31, 2007 By Maciej Dobrzanski 16 Comments

Microslow patch is used by many DBAs and developers to accurately time their queries and to catch those which run less than a second as they can also be a performance killer for a busy application. Recently I have started the development of an updated version of the patch. The basic idea is the same [...]

Filed Under: Percona Software Tagged With: InnoDB, Patches, Replication

InnoDB: look after fragmentation

Air traffic queries in InfiniDB: early alpha

How (not) to find unused indexes

Analyzing air traffic performance with InfoBright and MonetDB

Why InnoDB index cardinality varies strangely

A micro-benchmark of stored routines in MySQL

Just do the math!

Dropping unused indexes

ANALYZE: MyISAM vs Innodb

The new cool MySQL patch has landed! Check your queries performance!

Search

Top Searches

Percona Webinars

ABOUT PERCONA

Percona MySQL Training

Percona Careers

SOFTWARE FROM PERCONA

SERVICES FOR MYSQL USERS

ABOUT THIS BLOG AND PERCONA