Showing entries 71 to 80 of 103
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: optimization (reset)
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:

PLAIN TEXT CODE:

  1. SELECT * FROM `table` WHERE (col1='A'||col1='B') ORDER BY id DESC LIMIT 20 OFFSET 0

This column in the table is looks like this:

PLAIN TEXT CODE:

  1. `col1` enum('A','B','C','CD','DE','F','G','HI') default NULL

The table have 549252 rows and of course, there is an index on the col1. MySQL estimated the cardinality of that index as 87, though what was of course misleading as index cardinality in this case can't be over 9, as there is only 8(+ NULL) different possible values for this column.

PLAIN TEXT CODE:

[Read more]
How to tell when using INFORMATION_SCHEMA might crash your database

There are those that are very adamant about letting people know that using INFORMATION_SCHEMA can crash your database. For example, in making changes to many tables at once Baron writes:

“querying the INFORMATION_SCHEMA database on MySQL can completely lock a busy server for a long time. It can even crash it. It is very dangerous.”

Though Baron is telling the truth here, he left out one extremely important piece of information: you can actually figure out how dangerous your INFORMATION_SCHEMA query will be, ahead of time, using EXPLAIN.


In MySQL 5.1.21 and higher, not only were optimizations made to the INFORMATION_SCHEMA, but new values were added so that EXPLAIN had better visibility into what MySQL is actually doing. As per …

[Read more]
Follow-up To Loading CSS And JS Conditionally

First of all, I'd like to thank everyone who read and gave their 2 cents about the [WordPress Plugin Development] How To Include CSS and JavaScript Conditionally And Only When Needed By The Posts post. The article was well received and will hopefully spark some optimizations around loading styles and scripts.

Here are some discussions and mentions around the web:

[Read more]
Free MySQL Cluster Performance Tuning webinar – TODAY!

MySQL Cluster Performance Tuning Best Practices

Are you experiencing current performance bottlenecks in your high availability applications ? Are you designing a new mission-critical application and want to know how best to structure your schema and index strategy for optimal performance? Interested in how to transform your SQL into faster, more efficient queries?

Then this free web presentation is for you! You will get expert insight and learn best practices to help you identify those areas of database and application design that will give you the greatest benefits for performance when using MySQL Cluster.

We will discuss guidelines and best practices covering the following areas:

  • General Design Concepts and Guidelines
  • Schema Optimization
    • BLOB/Text vs VARBINARY/VARCHAR
    • Partition by Key
  • Index Selection and Tuning
[Read more]
Indexing text columns in MySQL

This time, I’m talking about indexes for string typed columns. In particular, I’ll show a procedure I find useful while looking for good index length values for these columns. I’ll use a sample table called people. Here’s what it looks like: mysql> desc people; +————+——————+——+—–+———+—————-+ | Field | Type | Null | Key | Default … Continue reading Indexing text columns in MySQL →

Related posts:

  1. Using MySQL Proxy to benchmark query performance By transparently sitting between client and server on each request,...
  2. Making use of …
[Read more]
Log Buffer #150

This is the 150th edition of Log Buffer, the weekly review of database blogs. Someone accidentally left Dave Edwards‘ cage unlocked, and he escaped, thus leaving me with the pleasurable duty of compiling the 150th weekly Log Buffer.

Many people other than Dave are finding release this week. Giuseppe Maxia explains some details of MySQL’s New Release Model. Andrew Morgan announces a New MySQL Cluster Maintenance Release. Aleksandr Kuzminsky of the MySQL Performance …

[Read more]
webinar on Data Reduction and Smoothing in MySQL

If you have missed Michael McFadden's session at the last MySQL Conference, here's a chance to catch up.

On June 11, at 17:00 UTC Michael McFadden will present at a free webinar, on the subject of Faster Data Reduction and Smoothing for Analysis & Archival in MySQL.

Don't let the "For ISVs" distract you. This session is a collection of very practical and down to earth tips for tasks that can be in the TODO list of any DBA.

In addition to being practical, …

[Read more]
webinar on Data Reduction and Smoothing in MySQL

If you have missed Michael McFadden's session at the last MySQL Conference, here's a chance to catch up.

On June 11, at 17:00 UTC Michael McFadden will present at a free webinar, on the subject of Faster Data Reduction and Smoothing for Analysis & Archival in MySQL.

Don't let the "For ISVs" distract you. This session is a collection of very practical and down to earth tips for tasks that can be in the TODO list of any DBA.

In addition to being practical, …

[Read more]
webinar on Data Reduction and Smoothing in MySQL

If you have missed Michael McFadden's session at the last MySQL Conference, here's a chance to catch up.

On June 11, at 17:00 UTC Michael McFadden will present at a free webinar, on the subject of Faster Data Reduction and Smoothing for Analysis & Archival in MySQL.

Don't let the "For ISVs" distract you. This session is a collection of very practical and down to earth tips for tasks that can be in the TODO list of any DBA.

In addition to being practical, …

[Read more]
Multi Direction Sorts and avoiding a file sort

There are two PRIMARY directions to sort data in SQL: Ascending (ASC) and Descending DESC.
When these two sort definitions are put together in a single statement a filesort is produced.

Why do we want to avoid filesorts?

Filesorts are bad. 1st they tickle a thread based buffer called sort_buffer_size. Additionally filesorts reads the data twice, unless max_length_for_sort_data limit is reached and as a result the Filesort runs slower to reduce disk I/O. If you want filesorts to run faster at the expense of the disk increase the default max_length_for_sort_data. You can read the filesort algorithm here.

So, here is an example


CREATE TABLE `ABCD` (
`A` int(10) unsigned NOT NULL default '0',
`B` int(10) unsigned NOT NULL default '0',
`C` int(10) unsigned NOT NULL …
[Read more]
Showing entries 71 to 80 of 103
« 10 Newer Entries | 10 Older Entries »