Search Results for: small query

Shard-Query adds parallelism to queries

Preamble: On performance, workload and scalability: MySQL has always been focused on OLTP workloads. In fact, both Percona Server and MySQL 5.5.7rc have numerous performance improvements which benefit workloads that have high concurrency. Typical OLTP workloads feature numerous clients (perhaps hundreds or thousands) each reading and writing small chunks of data. The recent improvements to […]

Beware large Query_Cache sizes

During last couple of months I had number of consulting projects with MySQL Query Cache being source of serious problems. One would see queries both for MyISAM and Innodb tables mysteriously stalling for few seconds with “NULL” in State column. These could be troubleshooted to waiting on Query Cache which at the same time had […]

MySQL Query Cache and prepared statements – support comming ?

Looking at post by Konstantin Osipov we should finally get Query Cache working with prepared statements in MySQL 5.1 The interesting thing Konstantin notes it just took few days to fix it – I believe MySQL Support Team alone spent much more times explaining customers why is it not done or troubleshooting cases with wrong […]

MySQL: Followup on UNION for query optimization, Query profiling

Few days ago I wrote an article about using UNION to implement loose index scan. First I should mention double IN also works same way so you do not have to use the union. So changing query to:

So as you see there are really different types of ranges in MySQL. IN range allows […]

MySQL Query Cache

MySQL has a great feature called “Query Cache” which is quite helpful for MySQL Performance optimization tasks but there are number of things you need to know. First let me clarify what MySQL Query Cache is – I’ve seen number of people being confused, thinking MySQL Query Cache is the same as Oracle Query Cache […]

Profiling MySQL queries from Performance Schema

When optimizing queries and investigating performance issues, MySQL comes with built in support for profiling queries aka SET profiling = 1; . This is already awesome and simple to use, but why the PERFORMANCE_SCHEMA alternative? Because profiling will be removed soon (already deprecated on MySQL 5.6 ad 5.7); the built-in profiling capability can only be enabled per session. This […]

InnoDB locks and deadlocks with or without index for different isolation level

Recently, I was working on one of the issue related to locks and deadlocks with InnoDB tables and I found very interesting details about how InnoDB locks and deadlocks works with or without index for different Isolation levels. Here, I would like to describe a small test case about how SELECT ..FOR UPDATE (with and without limit) behave […]