Emergency

Search Results for: mysql explain select_type optimize

Getting around optimizer limitations with an IN() list

There was a discussion on LinkedIn one month ago that caught my eye: Database search by “within x number of miles” radius? Anyone out there created a zipcode database and created a “search within x …

Read More
 

A micro-benchmark of stored routines in MySQL

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 …

Read More
 

Beware of MySQL Data Truncation

Here is nice gotcha which I’ve seen many times and which can cause just a minefield for many reasons. Lets say you had a system storing articles and you use article_id as unsigned int. As …

Read More
 

Multi-Column IN clause – Unexpected MySQL Issue

We have an application which stores massive amount of urls. To save on indexes instead of using URL we index CRC32 of the URL which allows to find matching urls quickly. There is a bit …

Read More
 

MySQL Performance – eliminating ORDER BY function

One of the first rules you would learn about MySQL Performance Optimization is to avoid using functions when comparing constants or order by. Ie use indexed_col=N is good. function(indexed_col)=N is bad because MySQL Typically will …

Read More
 

MySQL VIEW as performance troublemaker

I start to see applications being built utilizing VIEWs functionality which appeared in MySQL 5.0 and quite frequently VIEWs are used to help in writing the queries – to keep queries simple without really thinking …

Read More
 

Getting MySQL to use full key length

There is one bug, or “missing feature” in MySQL Optimizer which may give you hard time causing performance problems which may be hard to track down, it is using only part of the index when …

Read More
 

MySQL Optimizer and Innodb Primary Key

Innodb primary key is special in many senses and I was always wondering how well MySQL is integrated with Innodb to take advantage of these special features. Lets see which things work and which things …

Read More
 

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 …

Read More
 

Using UNION to implement loose index scan in MySQL

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 …

Read More