Showing entries 31 to 40 of 80
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: optimization (reset)
Statement-based vs Row-based Replication

Replication as most people know it, has mostly been SQL statement propagation from master to slave. This is known as "statement-based" replication. But there is also another kind of replication that is available, "the row-based replication" and that has quite a lot of benefits. In this post I intend on highlighting the advantages and disadvantages of both the types of replication to help you choose the best one. I also follow up with my own recommendation.

Find friends of friends using MySQL

In a previous article, I've already talked about an optimized way to connect locations in a geographic point of view by using MySQL. In this manner, locations of pubs, drugstores, barbers or even users can be obtained. Communities, or perhaps I should use the newer term Social Networks, make use of the buddy network of indiviual members in addition to the geographical mapping. This has many psychological advantages, because new members can be integrated in an established network very easily and I'm more willing to become involved when I already know some of the members.

Read More »

Is it possible to avoid query parsing inside of MySQL?

I've just started learning MySQL's internals but I've got an idea which I want to convert to a question here, to ask people who are already deeper in it. Is it a bad idea to completeley avoid query parsing on the server side and use a binary protocol instead? This way the client parses the query and could cache the statement structure for further usage or another client API uses a NoSQL approach to send the request data to the server.

Read the rest »

Tech Messages | 2010-12-21

A special extended edition of Tech Messages for 2010-12-14 through 2010-12-21:

[Read more]
Finding My Way

I am preparing a series of posts related to Sudoku. I am revisiting the “SQL only” solution I posted somewhere else a long time ago…  This time, we’ll get serious and optimize everything we can!  This lemon will be squeezed to the maximum!

Start your Pharo image (not mandatory since I will provide all necessary SQL scripts) and MySQL server as we’ll try to solve some Sudoku puzzles only with one SQL statement (no stored procedures or functions)!

Part 1 coming soon!

MySQL Query Optimization – Tip # 1 – Avoid using wildcard character at the start of a LIKE pattern.

The more I go through others SQL, there are some common mistakes that I see developers making over and over again, so I thought why not start a series of tips that can help developers optimize their queries and avoid common pitfalls. So this post is a part of that series of tips, and this is the first tip "Avoid using a wild card character at the start of a LIKE pattern".

MySQL Paginated displays – How to kill performance vs How to improve performance!

Pagination is used very frequently in many websites, be it search results or most popular posts they are seen everywhere. But the way how it is typically implemented is naive and prone to performance degradation. In this article I attempt on explaining the performance implications of poorly designed pagination implementation. I have also analyzed how Google, Yahoo and Facebook handle pagination implementation. Then finally i present my suggestion which will greatly improve the performance related to pagination.

Resolve many-to-many relations a bit different with MySQL

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 is very MySQL-oriented.

Read the rest »

How to Improve Query Cache Performance

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 “cacheable”, it must have the following properties:

  • It must be deterministic. The query must return the same result set each time that it is run. This means that it may not contain any non-deterministic variables, such as …
[Read more]
Improving MySQL Productivity – From Design to Implementation

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 Productivity View more presentations from Ronald Bradford.

Showing entries 31 to 40 of 80
« 10 Newer Entries | 10 Older Entries »