Emergency

Search Results for: select rows

Speed up GROUP BY queries with subselects in MySQL

We usually try to avoid subselects because sometimes they force the use of a temporary table and limits the use of indexes. But, when is good to use a subselect? This example was tested over …


How rows_sent can be more than rows_examined?

When looking at queries that are candidates for optimization I often recommend that people look at rows_sent and rows_examined values as available in the slow query log (as well as some other places). If rows_examined …


How to recover deleted rows from an InnoDB Tablespace

In my previous post I explained how it could be possible to recover, on some specific cases, a single table from a full backup in order to save time and make the recovery process more …


FictionPress Selects TokuDB for Consistent Performance and Fast Disaster Recovery

FictionPress Issues addressed: Support complex and efficient indexes at 100+ million rows. Predicable and consistent performance regardless of data size growth. Fast recovery. Ensuring Predictable Performance at Scale The Company:  FictionPress operates both FictionPress.com and …


SELECT UNION Results INTO OUTFILE

Here’s a quick tip I know some of us has overlooked at some point. When doing SELECT … UNION SELECT, where do you put the the INTO OUTFILE clause? On the first SELECT, on the …


Online Advertiser Intent Media Selects TokuDB over InnoDB and NoSQL for Big Data Ad-Hoc Analysis

Intent Media Issue addressed: Ad hoc analytics on clickstream data arriving too fast for InnoDB or NoSQL to handle. TokuDB powers an online advertising application The Company: Headquartered in New York, Intent Media is a …


When the subselect runs faster

A few weeks ago, we had a query optimization request from one of our customer. The query was very simple like:

This column in the table is looks like this:

The table have …


Missing Data – rows used to generate result set

As Baron writes it is not the number of rows returned by the query but number of rows accessed by the query will most likely be defining query performance. Of course not all row accessed …


To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?

When we optimize clients’ SQL queries I pretty often see a queries with SQL_CALC_FOUND_ROWS option used. Many people think, that it is faster to use this option than run two separate queries: one – to …


Using index for ORDER BY vs restricting number of rows.

One interesting problem with MySQL Optimizer I frequently run into is making poor decision when it comes to choosing between using index for ORDER BY or using index for restriction. Consider we’re running web site …