A

So I was wondering.... sometimes you dont know if you need to define your search a little more by adding another column and another variable.
What do I mean? I mean this:

select * from table where something = 1
so the 'something = 1' part defines the search.

BUT, what if you sometimes need to use 'something = 1' and sometimes you would just like it to search everything?


Doing it in the application layer/logic

Well, basically, what I used to do is play around with it in my application. For example:

$sqlstr = 'select * from table';
if ($something > 0 ){
$sqlstr = ' where something = 1';
}
As you can see, I first made the generic search. In case that I need to define it further, I simply add to the existing string with the search criteria.
But I always wondered, how can you just do it directly in the SQL statement.. …

This release fixes some minor bugs. It also adds a few major new features to several of the tools. The command-line option and help functionality, as well as several other pieces of common functionality, has been replaced with common modules that have a test suite. This is the first step towards a gradual rewrite to replace untested 'script' code with tested, maintainable code.

Si Chen wrote recently about the growth limits of open-source projects. He points out that as a project becomes larger, it gets harder to maintain. I can only agree. As the MySQL Toolkit project has grown, it’s become significantly more work to maintain, document, and enhance. (This is why I’m asking you to sponsor me for a week off my regular job to work on MySQL Table Sync, by the way.

One of the enhancements I added to MySQL Archiver in the recent release was listed innocently in the changelog as “Destination plugins can now rewrite the INSERT statement.” Not very exciting or informative, huh? Keep reading. If you’ve used plugins with MySQL Archiver you know that I created a series of “hooks” where plugins can take some action: before beginning, before archiving each row, etc etc. This lets plugins do things like create new destination tables, aggregate archived rows to summary tables during archiving (great for building data warehouses, though not as sophisticated as Kettle), and so on.

Mikio Hirabayash released his TokyoCabinet DB. So I took a minute this morning to play around with it!


mysql> INSTALL PLUGIN tokyo SONAME 'libtokyo_engine.so';
Query OK, 0 rows affected (0.00 sec)

mysql> show plugins;
+------------------+--------+--------------------+-----------------------+---------+
| Name | Status | Type | Library | License |
+------------------+--------+--------------------+-----------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | …

I now got answers to the second portions of the questions you asked Heikki. If you have not seen the first part it can be found here.

Same as during last time I will provide my comments for some of the answers under PZ and will use HT for original Heikkis answer.

Q26: You also say on Unix/Linux only one read-ahead can happen at the same time. How many read-aheads can be waiting in Queue when or Innodb will schedule more read-aheads only when given read-ahead is completed?

HT: A query thread normally posts about 64 page read requests per a readahead. The InnoDB buffer is 256 page read requests for the aio read thread. Thus, about 4 readaheads can be in the queue at a time.

The read aio thread will normally read all the 64 page read …

Installing software on the Mac is usually a no-brainer: You get a ZIP archive or disk image that gets uncompressed or mounted for you. What is left for you is to move whatever application to your Applications folder. Done.

In case of a disk image, developers often go so far to include a reference to your Applications folder on the disk image itself. So all you have to do is to drag an icon onto another one less than 200 pixels away. This works so nicely on the Mac (the advantages of a controlled environment) because in the majority of cases, it is at /Applications on your filesystem.

To make that even easier, developers usually put nice background images onto those disk images to illustrate (with an arrow for example), what the user has to do. Skype, among others, nail this experience. (I’m not talking about the ZIP-only approach here). They have, on their download page, a step-by-step guide accompanied …

This release fixes some minor bugs. It also adds a few major new features to several of the tools. The command-line option and help functionality, as well as several other pieces of common functionality, has been replaced with common modules that have a test suite. This is the first step towards a gradual rewrite to replace untested ‘script’ code with tested, maintainable code. Changelog for mysql-archiver: 2007-11-04: version 1.0.2 * Made command-line help easier to use.

My last post about Innodb Performance Optimization got a lot of comments choosing proper innodb_buffer_pool_size and indeed I oversimplified things a bit too much, so let me write a bit better description.

Innodb Buffer Pool is by far the most important option for Innodb Performance and it must be set correctly. I've seen a lot of clients which came through extreme sufferings leaving it at default value (8M). So if you have dedicated MySQL Box and you're only using Innodb tables you will want to give all memory you do not need for other needs for Innodb Buffer Pool.

This of course assumes your database is large so you need large buffer pool, if not - setting buffer pool a bit larger than your database size will be enough. You also should account for growth of course. You need buffer pool a bit (say …