You searched for mysql difference between index and multiple key

MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark

March 11, 2013 By Justin Swanhart 7 Comments

So far most of the benchmarks posted about MySQL 5.6 use the sysbench OLTP workload. I wanted to test a set of queries which, unlike sysbench, utilize joins. I also wanted an easily reproducible set of data which is more rich than the simple sysbench table. The Star Schema Benchmark (SSB) seems ideal for this. [...]

Filed Under: MySQL Tagged With: InnoDB plugin, Justin Swanhart, MySQL 5.5, MySQL 5.6, Star Schema Benchmark (SSB), sysbench

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

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

MySQL Upgrade Webinar Questions Followup

June 28, 2012 By Peter Zaitsev 7 Comments

I did a Webinar about MySQL Upgrade – Best Practices Yesterday and there were some questions we could not answer during Webinar, following Jay’s Lead I decided to post them as a Blog Post. Q: Can you go directly MySQL 5.0 to 5.5 for MyISAM tables? MyISAM have not been getting any significant development since [...]

Filed Under: Insight for DBAs, MySQL

Data mart or data warehouse?

July 15, 2010 By Justin Swanhart 13 Comments

This is part two in my six part series on business intelligence, with a focus on OLAP analysis. Part 1 – Intro to OLAP Identifying the differences between a data warehouse and a data mart. (this post) Introduction to MDX and the kind of SQL which a ROLAP tool must generate to answer those queries. [...]

Filed Under: Insight for Developers, MySQL

Why you should ignore MySQL’s key cache hit ratio

February 28, 2010 By Baron Schwartz 28 Comments

I have not caused a fist fight in a while, so it’s time to take off the gloves. I claim that somewhere around of 99% of advice about tuning MySQL’s key cache hit ratio is wrong, even when you hear it from experts. There are two major problems with the key buffer hit ratio, and [...]

Filed Under: Insight for DBAs, MySQL Tagged With: key_buffer, key_buffer_size, MyISAM, MyISAM Key Cache

Multi Column indexes vs Index Merge

September 19, 2009 By Peter Zaitsev 31 Comments

The mistake I commonly see among MySQL users is how indexes are created. Quite commonly people just index individual columns as they are referenced in where clause thinking this is the optimal indexing strategy. For example if I would have something like AGE=18 AND STATE=’CA’ they would create 2 separate indexes on AGE and STATE [...]

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

Multiple column index vs multiple indexes

August 22, 2008 By Peter Zaitsev 13 Comments

After my previous post there were questions raised about Index Merge on Multiple Indexes vs Two Column Index efficiency. I mentioned in most cases when query can use both of the ways using multiple column index would be faster but I also went ahead to do some benchmarks today.

Filed Under: Benchmarks, Insight for DBAs

ORDER BY … LIMIT Performance Optimization

September 1, 2006 By Peter Zaitsev 100 Comments

Suboptimal ORDER BY implementation, especially together with LIMIT is often the cause of MySQL Performance problems. Here is what you need to know about ORDER BY … LIMIT optimization to avoid these problems ORDER BY with LIMIT is most common use of ORDER BY in interactive applications with large data sets being sorted. On many [...]

Filed Under: Insight for Developers Tagged With: Optimizer, Tips

Using UNION to implement loose index scan in MySQL

August 10, 2006 By Peter Zaitsev 15 Comments

One little known fact about MySQL Indexing, however very important for successfull MySQL Performance Optimization is understanding when exactly MySQL is going to use index and how it is going to do them. So if you have table people with KEY(age,zip) and you will run query something like SELECT name FROM people WHERE age BETWEEN [...]

Filed Under: Insight for Developers Tagged With: Optimizer, Tips

MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark

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

Innodb Table Locks

MySQL Upgrade Webinar Questions Followup

Data mart or data warehouse?

Why you should ignore MySQL’s key cache hit ratio

Multi Column indexes vs Index Merge

Multiple column index vs multiple indexes

ORDER BY … LIMIT Performance Optimization

Using UNION to implement loose index scan in MySQL

Search

Top Searches

Percona Webinars

ABOUT PERCONA

Percona MySQL Training

Percona Careers

SOFTWARE FROM PERCONA

SERVICES FOR MYSQL USERS

ABOUT THIS BLOG AND PERCONA