You searched for mysql myisam select primary key

Benchmarking Percona Server TokuDB vs InnoDB

May 7, 2013 By Vadim Tkachenko 29 Comments

After compiling Percona Server with TokuDB, of course I wanted to compare InnoDB performance vs TokuDB. I have a particular workload I’m interested in testing – it is an insert-intensive workload (which is TokuDB’s strong suit) with some roll-up aggregation, which should produce updates in-place (I will use INSERT .. ON DUPLICATE KEY UPDATE statements [...]

Filed Under: Benchmarks, MySQL, Percona Server Tagged With: Benchmarking, InnoDB, Percona Server, TokuDB

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

February 26, 2013 By Ernie Souhrada 1 Comment

I’ve never been a very big fan of MyISAM; I would argue that in most situations, any possible advantages to using MyISAM are far outweighed by the potential disadvantages and the strengths of InnoDB. However, up until MySQL 5.6, MyISAM was the only storage engine with support for full-text search (FTS). And I’ve encountered many [...]

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: Ernie Souhrada, FTS, FullText Search, InnoDB, MyISAM, MySQL 5.6

Adventures in archiving

February 11, 2013 By David Busby 16 Comments

One of our Remote DBA service clients recently had an issue with size on disk for a particular table; in short this table was some 25 million rows of application audit data with an on disk size of 345GB recorded solely for the purposes of debugging which may or may not occur. Faced with the task of [...]

Filed Under: MySQL Tagged With: archiving, David Busby, Remote DBA

Concatenating MyISAM files

November 19, 2012 By Yves Trudeau 5 Comments

Recently, I found myself involved in the migration of a large read-only InnoDB database to MyISAM (eventually packed). The only issue was that for one of the table, we were talking of 5 TB of data, 23B rows. Not small… I calculated that with something like insert into MyISAM_table… select * from Innodb_table… would take [...]

Filed Under: MySQL

Full Text Search Webinar Questions Followup

August 24, 2012 By Bill Karwin 5 Comments

I presented a webinar this week to give an overview of several Full Text Search solutions and compare their performance. Even if you missed the webinar, you can register for it, and you’ll be emailed a link to the recording. During my webinar, a number of attendees asked some good questions. Here are their questions and my [...]

Filed Under: Events and Announcements, Insight for Developers, MySQL

MySQL Indexing Best Practices: Webinar Questions Followup

August 16, 2012 By Peter Zaitsev 4 Comments

I had a lot of questions on my MySQL Indexing: Best Practices Webinar (both recording and slides are available now) We had lots of questions. I did not have time to answer some and others are better answered in writing anyway. Q: One developer on our team wants to replace longish (25-30) indexed varchars with [...]

Filed Under: Insight for DBAs, MySQL

Innodb Table Locks

July 31, 2012 By Peter Zaitsev 4 Comments

Innodb uses row level locks right ? So if you see locked tables reported in SHOW ENGINE INNODB STATUS you might be confused and rightfully so as Innodb table locking is a bit more complicated than traditional MyISAM table locks. Let me start with some examples. First lets run SELECT Query:

---TRANSACTION 12303, ACTIVE 26 sec
mysql tables in use 2, locked 0
MySQL thread id 53038, OS thread handle 0x7ff759b22700, query id 3918786 localhost root Sending data
select count(*) from sbtest,sbtest x
Trx read view will not see trx with id >= 12304, sees < 12301

---TRANSACTION 12303, ACTIVE 26 sec

mysql tables in use 2, locked 0

MySQL thread id 53038, OS thread handle 0x7ff759b22700, query id 3918786 localhost root Sending data

select count(*) from sbtest,sbtest x

Trx read view will not see trx with id >= 12304, sees < 12301

As you can [...]

Filed Under: Insight for DBAs, MySQL

A recovery trivia or how to recover from a lost ibdata1 file

June 3, 2011 By Yves Trudeau 8 Comments

A few day ago, a customer came to Percona needing to recover data. Basically, while doing a transfer from one SAN to another, something went wrong and they lost the ibdata1 file, where all the table meta-data is stored. Fortunately, they were running with innodb_file_per_table so the data itself was available. What they could provide [...]

Filed Under: MySQL

Checking the subset sum set problem with set processing

May 16, 2011 By Justin Swanhart Leave a Comment

Hi, Here is an easy way to run the subset sum check from SQL, which you can then distribute with Shard-Query:

CREATE TABLE `the list` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `val` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `id` (`id`)
) ENGINE=MyISAM;
SELECT val as `val`, COUNT(DISTINCT (id)) as `cd`
FROM  test.data as d  WHERE val in (-2,-3,-10,15,15,16)
GROUP BY val;
+-----+----------+----------+
| val | cd       | CNT      |
+-----+----------+----------+
| -10 |        1 |        1 |
|  -3 |        1 |        1 |
|  -2 |        1 |        1 |
|  15 | 35417088 | 35417088 |
+-----+----------+----------+
5 rows in set (40.20 sec)

CREATE TABLE `the list` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`val` bigint(20) NOT NULL DEFAULT '0',

PRIMARY KEY (`id`),

KEY `id` (`id`)

) ENGINE=MyISAM;

SELECT val as `val`, COUNT(DISTINCT (id)) as `cd`

FROM test.data as d WHERE val in (-2,-3,-10,15,15,16)

GROUP BY val;

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

| val | cd | CNT |

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

| -10 | 1 | 1 |

| -3 | 1 | 1 |

| -2 | 1 | 1 |

| 15 | 35417088 | 35417088 |

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

5 rows in set (40.20 sec)

Notice there is no 16 in the list. We did not pass the check. There are enough 15s though. The distinct value count for each item in the output set, must at least [...]

Filed Under: MySQL

Shard-Query turbo charges Infobright community edition (ICE)

May 6, 2011 By Justin Swanhart 23 Comments

Shard-Query is an open source tool kit which helps improve the performance of queries against a MySQL database by distributing the work over multiple machines and/or multiple cores. This is similar to the divide and conquer approach that Hive takes in combination with Hadoop. Shard-Query applies a clever approach to parallelism which allows it to [...]

Filed Under: MySQL

Benchmarking Percona Server TokuDB vs InnoDB

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

Adventures in archiving

Concatenating MyISAM files

Full Text Search Webinar Questions Followup

MySQL Indexing Best Practices: Webinar Questions Followup

Innodb Table Locks

A recovery trivia or how to recover from a lost ibdata1 file

Checking the subset sum set problem with set processing

Shard-Query turbo charges Infobright community edition (ICE)

Search

Top Searches

Percona Webinars

ABOUT PERCONA

Percona MySQL Training

Percona Careers

SOFTWARE FROM PERCONA

SERVICES FOR MYSQL USERS

ABOUT THIS BLOG AND PERCONA