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

Displaying posts with tag: Ideas (reset)

An interesting case in ORDER BY LIMIT optimization
+1 Vote Up -0Vote Down

Recently, I was asked about an interesting case in ORDER BY … LIMIT optimization. Consider a table

create table tbl (
  …
  KEY key1(col1, col2),
  PRIMARY KEY (pk)
) engine=InnoDB;

Consider queries like:

  select * from tbl where col1=’foo’ and col2=123 order by pk limit 1;
  select * from tbl where col1=’bar’ and col2=123 order by pk limit 1;

These run nearly instantly. But, if one combines these two queries with col1='foo' and col1='bar' into one query with col1 IN ('foo','bar'):

  select * from tbl where col1 IN (’foo’,'bar’) and col2=123 order by pk limit 1;

then the query is be …

  [Read more...]
Fun with Bugs #8 - what's wrong with Oracle's way of handling public MySQL bugs database
+1 Vote Up -0Vote Down

Many people seem unhappy with the way Oracle develops MySQL. I am not one of them. I think very few really important things are missing and in this post I'd like to concentrate on one of them: having internal and public bugs databases not in sync in too many cases.

Let me quote myself to explain where problem starts:

"Now the most important thing you should know about MySQL bugs processing the way it is done now in Oracle. When bug is "Verified" and(!) considered serious enough, it is copied to the …



  [Read more...]
Here’s a quick way to Foresee if Replication Slave is ever going to catch up and When!
+1 Vote Up -0Vote Down

If you ever had a replication slave that is severely behind, you probably noticed that it’s not catching up with a busy master at a steady pace. Instead, the “Seconds behind master” is going up and down so you can’t really tell whether the replica is catching up or not by looking at just few samples, unless these are spread apart. And even then you can’t tell at a glance when it is going to catch up.

Normally, the “severely behind” thing should not happen, but it does often happen in our consulting practice:

  • sometimes replication would break and then it needs to …
  [Read more...]
help starting my sql ideas
+0 Vote Up -2Vote Down

The Brief
The Client’s mystery shopping programme consists of visits to each of the client’s ten locations, grouped into five different areas. Each location receives a single visit each month, but on occasion a visit may not take place.
On the completion of each visit, a questionnaire is completed by the mystery shopper that is used to rate the service they received during the visit. The completed questionnaire is assigned a score, which is calculated based on the number of points achieved over the number of points available, normally expressed as a percentage.
Points Achieved x 100 Points Available
= % Score
PHP Development Test






  [Read more...]
Optimizing slow web pages with mk-query-digest
+1 Vote Up -0Vote Down

I don’t use many tools in my consulting practice but for the ones I do, I try to know them as best as I can. I’ve been using mk-query-digest for almost as long as it exists but it continues to surprise me in ways I couldn’t imagine it would. This time I’d like to share a quick tip on how mk-query-digest allows you to slice your data in a completely different way than it otherwise would by default.

Disclaimer: this only works when persistent connections or connection pools aren’t used and is only accurate when single …

  [Read more...]
Tuning MySQL Server Settings
+0 Vote Up -0Vote Down

The default configuration file for MySQL is intended not to use many resources, because its a general purpose sort of a configuration file. The default configuration does enough to have MySQL running happily with limited resources and catering to simple queries and small data-sets. The configuration file would most definitely need to be customized and tuned if you intend on using complex queries and when you have good amount of data. Most of the tunings mentioned in this post are applicable to the MyISAM storage engine, I will soon be posting tunings applicable to the Innodb storage engine. Getting started...

Databases: Normalization or Denormalization. Which is the better technique?
+1 Vote Up -0Vote Down

This has really been a long debate as to which approach is more performance orientated, normalized databases or denormalized databases. So this article is a step on my part to figure out the right strategy, because neither one of these approaches can be rejected outright. I will start of by discussing the pros and cons of both the approaches. Pros and Cons of a Normalized database design. Normalized databases fair very well under conditions where the applications are write-intensive and the write-load is more than the read-load. This is because of the following reasons: Normalized tables are usually smaller and...

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...

Ideas for select all columns but one mysql stored procedure
+0 Vote Up -0Vote Down

Assume we’ve a table with 100 rows and we need to select all columns but one. The problem is headache of actually typing out all 99 required columns!! Solutions / Ideas to above problem are: Ignorance is bliss. Select all(*) and ignore the column. Manually type column names or manage it with copy paste! Create […]

Active Cache for MySQL
+1 Vote Up -1Vote Down

One of the problems I have with Memcache is this cache is passive, this means it only stores cached data. This means application using Memcache has to has to special logic to handle misses from the cache, being careful updating the cache - you may have multiple data modifications happening at the same time. Finally you have to pay with increased latency constructing the items expired from the cache, while they could have been refreshed in the background. I think all of these problems could be solved with concept of active cache

The idea with Active Cache is very simple - for any data retrieval operation cache would actually know how to construct the …

  [Read more...]
Showing entries 1 to 10 of 24 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.