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

Displaying posts with tag: Query Optimization (reset)

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.

To calculate this, you take the total number of DISTINCT records and divide by the total number of records.

My company has a large Users table, so I grabbed some

  [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 INNER JOIN t2 USING (commonField);

The optimizer looks at the statistics for tables t1 and t2 and decides which is better:
1) To go through each item in t1, looking for a matching

  [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 database(s). Use MyISAM tables, because a reporting server can afford to be behind the master, and MyISAM is better for reporting — better metadata. For something like a “Users” table, make 2

  [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 you’re trying to enter is not in the ENUM column definition, MySQL throws an error.

ENUM is not a standard SQL data type. It is MySQL specific.

As an example, in the real world I run a database for an international not-for-profit. Whenever

  [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:

`uid` int(10) unsigned NOT NULL default ‘0′,
`voteDate` date NOT NULL default ‘0000-00-00′,
`uidVoteFor` int(10) unsigned NOT NULL default ‘0′,
PRIMARY KEY (`uid`,`voteDate`),
KEY `countVotes` (`uidVoteFor`)

There’s no need for a timestamp, and you can

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

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.