SQL

In MyISAM, we do have LOAD INDEX INTO CACHE. In InnoDB this does not work. For benchmarking I often require a way to preload the innodb_buffer_pool with the primary key and data after a server restart to shorten warmup phases.

According to Blackhole Specialist Kai, the following should work: Another win for the unbreakable BLACKHOLE storage engine.

Many maintenance procedures need to generate SQL as strings, and then need to execute that string as SQL. Use PREPARE for this.



(as requested on Freenode:#mysql)

Frank asked in Can MySQL triggers update another table just that.

Here is how to have a users table with a summary field, and a detail table with a value field. When records are inserted, modified or deleted with changing values in the detail table, the matching summaries in the users table are updated.

Last week I was with a customer and came across a side problem that was of a more general nature. It is the problem of users having a number of things that use up ressources. The things may be mails, recordings, stamps or whatever - for simplicity we assume recordings, and the ressource consumed is size. The system has a quota system in place, which stores a ressource limit per user.

The quota problem is: if a user is over quota, we want to get a number of item ids to delete per user from old to new, until that user is under quota or just one item over quota. This is the quota problem, and I needed a quota query that solves it.

Here is the setup.

The sieve of Eratosthenes finds prime numbers by creating a list of numbers, taking the smallest number in this list that is not stroken out and striking out all of its multiples, then going on to the next number that is not stroken out and so on.

For example, if the starting list is (2, 3, 4, 5, 6, 7, 8, 9) (we do leave out 1, because it is not a prime), then the first number from the list is 2, and therefore 4, 6 and 8 cannot be primes and are removed from the list: 2 is prime, and the list is now (3, 5, 7, 9). Lowest number from the list is 3, and therefore 6 and 9 cannot be primes, leaving (5, 7) and so on.

At work, I came across an interesting problem involving graphs for which I found no completely satisfactory solution.

A colleague tried to model applications and their dependencies as a directed graph. When editing dependencies for a given node, he wanted to show only nodes as new possible descendants which are not yet direct descendants of the current node. Additionally, when selecting new parents, nodes that are already direct parents should not be shown. Since he is still on MySQL 4.0, subselects could not be used.

Here is the data model and a bit of test data: