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

Displaying posts with tag: optimization (reset)

Resolve many-to-many relations a bit different with MySQL
+0 Vote Up -0Vote Down

In database modeling, a m:n relationship is usually resolved by an additional table. But what if this relation is used only for archiving and the number of links in the resulting table is not too high? In that context, I got the idea to store all referring ID's as CSV string directly into a TEXT column of one of the referring tables. I came to this idea, because otherwise I would have to build complicated foreign keys and this way I also save one additional table. Certainly, this only makes sense if the data is not frequently accessed as foreign key. Nevertheless, I would like to tackle the problem, even if the implementation …

  [Read more...]
How to Improve Query Cache Performance
+0 Vote Up -0Vote Down

The MySQL query cache can be very useful in environments where data is not modified often, and traffic consists of mostly reads. It can improve performance by quite a bit if used correctly, but can actually degrade performance if configuration, queries, and traffic patterns are not optimized for it.

Let me quickly go over what the query cache is, and what it is not. The query cache does not cache the query execution plan, the full page on disk (which is what the InnoDB buffer pool is used for), DDL statements, or any queries that modify data (INSERT/UPDATE/etc). The query cache *does* cache the full result set of “cacheable” SELECT queries. For a query to be …

  [Read more...]
Improving MySQL Productivity – From Design to Implementation
+2 Vote Up -1Vote Down

My closing presentation at the dedicated MySQL track at ODTUG Kaleidoscope 2010 discussed various techniques and best practices for improving the ROI of developer resources using MySQL. Included in the sections on Design, Security, Development, Testing, Implementation, Instrumentation and Support were also a number of horror stories of not what to do, combined with practical examples of improving productivity.

Increasing MySQL …

  [Read more...]
Optimized way of getting subqueries at once using JSON
+0 Vote Up -0Vote Down

<p>Imagine we have a user table in MySQL and similarly, a products table. Each user can now busily buy items. But this is not what this article is all about. This article is more about, that users can also favor articles they like. From a database point of view, this represents a <em>m:n</em> relation, which is resolved by an additional table. We now want to display a list of all products and all users who favor a specific article. In real world scenarios, this can be a really big amount of data, but let's bring an approximation later into play and focus on the main idea for the moment. In addition we want to know, if the person …

  [Read more...]
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...]
10 Newer Entries Showing entries 31 to 40 of 73 10 Older Entries

Planet MySQL © 1995, 2016, 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.