Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
10 Newer Entries Showing entries 31 to 40 of 69 10 Older Entries

Displaying posts with tag: optimization (reset)

The “Shadow Table” trick.
+0 Vote Up -0Vote Down

The need: Often there is a requirement where data in a particular table has to be processed, and the data processing might be slow, while the table might be a one that is used by your application extensively. For example, a logging table that logs page hits. Or there might be an archiving operation that has to be performed on a particular table. Archiving / processing / aggregating records, all these operations are slow and can really blog down a website, combine that with the added overhead if the table that needs to have these operations performed is one that...

Performance tuning using vertical partitioning.
+0 Vote Up -0Vote Down

Does having small data-sets really help? Of course it does! Are memory lookups faster that disk lookups. Of course ! So many times I have seen people complain about queries taking too long now, while they were not taking that long earlier. There is one big reason for this, earlier the size of data-set was small so it could fit into memory. Now that the data-set has grown large enough that it cannot fit entirely into memory, the disk seeks really have slowed down the queries significantly. What to do now? Vertical partitioning. Divide the data-set into separate data-sets vertically....

MySQL Indexes – Multi-column indexes and order of columns
+0 Vote Up -0Vote Down

The problem: Many a times people find that they create index but the query is still slow or the index is not being used by MySQL to fetch the result-set. Mostly the reason is that the index is not created properly, either not the right columns being indexed or the order of columns in the index does not match how its being used in the query. The order of index! What’s that. Well that’s what we will be discussing today. How does the order of column in the index matter? The order of columns in the index matters a lot,...

sort_buffer_size and Knowing Why
+5 Vote Up -5Vote Down

In How to tune MySQL’s sort_buffer_size, Baron gives a condescending viewpoint on how to tune the sort_buffer_size variable in MySQL. In a much-nicer-nutshell, his advice is “do not change sort_buffer_size from the default.”

Baron did not explain the logic behind his reasoning, he handwaves that “people utterly ruin their server performance and stability with it,” but does not explain how changing the sort_buffer_size kills performance and stability. Regardless of how respected and knowledgeable the source, NEVER take any advice …

  [Read more...]
Videos of Pythian Sessions from the 2010 O’Reilly MySQL Conference and Expo
+2 Vote Up -0Vote Down

Here’s a sneak peek at a video matrix — this is all the videos that include Pythian Group employees at the MySQL conference. I hope to have all the rest of the videos processed and uploaded within 24 hours, with a matrix similar to the one below (but of course with many more sessions).


Title Presenter Slides Video link
(hr:min:sec)
Details (Conf. site link)
Main Stage
Keynote: Under New Management: Next Steps for the Community Sheeri …
  [Read more...]
Write data asynchronously to MySQL
+0 Vote Up -0Vote Down

I think most developers are able to cache database queries by now. But what about DML queries? Every query, connection - or in the general case - ressource needs time. So I thought a lot about how to write data as efficiently as possible into the database - in my case MySQL. Hmm...We have to take a closer look at the details and we can't choose the same asynchronously writing method for every kind of query. With kind of query I mean that it depends on what storage engine we use, the complexity of the query, should more than one record be written at once, are triggers involved and so on. Sure, the one or the other query MUST be written instantly, but most of …

  [Read more...]
When the subselect runs faster
+1 Vote Up -1Vote Down

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 …

  [Read more...]
How to tell when using INFORMATION_SCHEMA might crash your database
+8 Vote Up -2Vote Down

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 …

  [Read more...]
Follow-up To Loading CSS And JS Conditionally
+0 Vote Up -0Vote Down

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!
Employee +2 Vote Up -0Vote Down

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 …

  [Read more...]
10 Newer Entries Showing entries 31 to 40 of 69 10 Older Entries

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.