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

Displaying posts with tag: Query Optimization (reset)

Picking Up Where You Left Off??
+0 Vote Up -0Vote Down

I started this as a response to Keith Murphy’s post at http://www.paragon-cs.com/wordpress/?p=54, but it got long, so it deserves its own post. The basic context is figuring out how not to cause duplicate information if a large INSERT statement fails before finishing.
Firstly, the surefire way to make sure there are no duplicates if you have a unique (or primary) key is to use INSERT IGNORE INTO.
Secondly, I just experimented with adding an index to an InnoDB table that had 1 million rows, and here’s what I got (please note, this is one experience only, the plural …

  [Read more...]
Top 10 MySQL Best Practices
+0 Vote Up -0Vote Down

So, O’Reilly’s ONLamp.com has published the “Top 10 MySQL Best Practices” at http://www.onlamp.com/pub/a/onlamp/2002/07/11/MySQLtips.html. Sadly, I find most “best practice” list do not thoroughly explain the “why” enough so that people can make their own decisions.
For instance, #3 is “Protect the MySQL installation directory from access by other users.” I was intrigued at what they would consider the “installation” directory. By reading the tip, they actually mean the data directory. They say nothing of the log directory, nor that innodb …

  [Read more...]
Progress report on High Performance MySQL, Second Edition
+0 Vote Up -0Vote Down

It's been a while since I've written about progress on the book. I actually stopped working on it as much at the beginning of the month, because on October 31(st) I managed to finish a first draft of the last big chapter! Now I'm back to full-time work at my employer, and I'm working on the book in the evenings and weekends only. Read on for details of what I've been working on and what's next in the pipeline.

Making Queries 45-90 Times Faster!!
+0 Vote Up -0Vote Down

aka…..”when good queries go bad!”
So, today the developers were debugging why a script was running much longer than expected. They were doing text database inserts, and got to the point where they realized that double the amount of text meant the queries took double the amount of time.
You see, they were doing similar text inserts over and over, instead of using connection pooling and/or batching them. Apparently the other DBA explained that it was a limitation of MySQL, but either the developers didn’t convey what they were doing well, or the DBA didn’t think to mention batching.  More »

Selectivity and Index Performance
+0 Vote Up -0Vote Down

Let’s look into selectivity, as this is an important topics when looking at index performance. (Oooh, I said “performance”, watch everyone’s ears perk up!).

This will probably answer the questions “Why isn’t MySQL using my index?” or “Why is my query so slow when I have an index on it?”

Selectivity describes how different values of a field are. It is a number from 0-1, although you can also think of it as a percentage. A value of 1, or 100%, means that each value in the field is unique. This happens with UNIQUE and PRIMARY keys, although non-unique fields may have a selectivity of 1 — for example, a timestamp value in a not-often-used table. …

  [Read more...]
The Care and Feeding of MySQL Tables
+0 Vote Up -0Vote Down

Our site went from weekly crashes during our two busiest nights to not even peeping this week (during the two busiest nights), and the only thing we changed was that we did some table maintenance. We hadn’t done table maintenance at least as long as I’ve been around, which is 6 months. We are a site with high volumes of both reads and writes. This article will talk about the care and feeding of tables; feel free to use this for justification to have a maintenance window, or even permission to run table maintenance statements.

MySQL uses a cost-based optimizer to best translate the written query into what actually happens. This means when you write:

SELECT foo FROM t1 …

  [Read more...]
One thing MERGE Tables Are Good For
+0 Vote Up -0Vote Down

Many people have some kind of reporting or auditing on their database. The problem is that the data grows very large, and lots of times there is data that can be purged. Sure, theoretically one never needs to purge data, but sometimes a “delete” flag just won’t work — when you search on the delete flag, a full table scan may be the most efficient way to go.

Of course, that’s not acceptable. And in many cases, say when you have users who no longer use the site but did in the past (and perhaps have billing data associated with them), you never want to get rid of them.

So what to do? Make a special reporting database, that gathers information from the production …

  [Read more...]
Working Smarter, Not Harder (SET and ENUM)
+0 Vote Up -0Vote Down

So, in a previous post I talked about smart code. Today I put myself square into a discussion about ENUM and SET. ENUM is an enumerated list of values; similar to a pull-down menu, where the only values allowed in that field are the ones defined, with the option of also having a null column.

The ENUM field takes the idea of normalizing the data and eliminates the need for a join on that data. It also makes data integrity easy — if the value …

  [Read more...]
Smart code
+0 Vote Up -0Vote Down

So, the other day I was asked by a developer to come up with a table to tally votes. Basically, there are 6 choices, and customers can vote once per day. I asked if there were any other constraints, and there were none. I specifically asked if they wanted once per calendar date, or ‘it has to be 24 hours since the last vote’; they wanted ‘once per calendar date’. And updating the current day’s vote is not allowed. Once the vote is in, it cannot be changed.

So I came up with a simple table:

CREATE TABLE `ManOfMonth` (
`uid` int(10) unsigned NOT NULL default ‘0′,
`voteDate` date NOT NULL default ‘0000-00-00′,
`uidVoteFor` int(10) unsigned NOT …


  [Read more...]
10 Newer Entries Showing entries 31 to 39

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