So I came across an odd situation today.

I have a system that creates memory tables using the PHP  $_COOKIE['PHPSESSID'] value.

Once some work is done it then drops the table.

Two sample tables are below for my example .


@@VERSION: 5.6.19-log
CREATE TABLE `f7a7a8d3a7ba75b5eb1712864c9b27eb` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=MEMORY;

CREATE TABLE `8865e52c7e1bea515e7156f240729275` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=MEMORY;


Now some simple processing occurs on the real tables I used then like I said I dropped them.
…

It all started with a goal to make InnoDB temporary tables more effective. Temporary table semantics are blessed with some important characteristics that can help us simplify lot of operations.

• Temporary tables are not visible across connections
• Temporary tables lifetime is limited to connection lifetime (unless user explicitly drops it).

What does this means in to InnoDB ?

• REDO logging can be avoided for temporary tables and related objects since temporary tables do not survive a shutdown or crash.
• Temporary table definitions can be maintained in-memory without persisting to the disk.
• Locking constraints can be relaxed since only one client can see these tables.
• Change buffering can be avoided since the majority of temporary tables are short-lived.

In order to implement these changes in InnoDB we took a bit different approach:

Hello everyone, here we go for a new blog post and now treating about InnoDB inside MySQL 5.5 and as you should now, this new version uses InnoDB Plugin version 1.1 which one has a lot of new adjustable resources. What most caught my attention was the impressed way that users could adjust it to have [...]

Permalink: http://bit.ly/VP174V



Certain complex MySQL SELECT and subquery statements will not allow the use of the * wildcard and you will need to fill in the entire column list of a given table. Consider the following simplified example, a SELECT statement that contains 3 columns. The asterisk here refers to all columns, which is actually the 3 columns listed in the GROUP BY clause:

SELECT
 IF(
  EXISTS(
  SELECT *
  FROM (
   SELECT *
   FROM `dbName_A`.`tableName_A`
   UNION ALL
   SELECT *
   FROM `dbName_B`.`tableName_B`
   AS `compareTables`
  GROUP BY `column_1`, `column_2`, `column_3`
  HAVING COUNT(*) = 1),
 1, 0);

Imagine if it were dozens of columns instead of just 3. You can't simply put in the * wildcard like 'GROUP BY * '. The above example will not …

MySQL keeps many different files, some contain real data, some contain meta data. Witch ones are important? Witch can your throw away?

This is my attempt to create a quick reference of all the files used by MySQL, whats in them, what can you do if they are missing, what can you do with them.

When I was working for Dell doing Linux support my first words to a customer where “DO YOU HAVE COMPLETE AND VERIFIED BACKUP?” Make one now before you think about doing anything I suggest here.

You should always try to manage your data through a MySQL client.  If things have gone very bad this may not be possible. MySQL may not start. If your file system get corrupt you may have missing files. Sometimes people create other files in the MySQL directory (BAD).  This should help you understand what is safe to remove.

Before you try to work with one of these files make sure you have the file permissions set …

Your searching for how to create a join across two databases on two different servers and it can’t be done directly.   select  d1.a, d2.b from db1@server1 join db2@server2 where db1.c = db2.c; does not work.

You learn about federated databases.  The federated storage engine allows accesses data in tables of remote databases.  Now how do you make it work?

1) Check if the federated storage engine is supported.  Federation is OFF by default!

mysql> show engines;
+------------+---------+----------------------------------------------------------------+
| Engine     | Support | Comment                                                        |
+------------+---------+----------------------------------------------------------------+
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     |
| MyISAM     | DEFAULT | Default engine as of …

Do you have MyISAM tables you reload with new data?

Do your queries, using that table, get blocked because the table is locked?

Do the waiting queries create idle connections slowing down the table load?

Do you wish you could just replace the table?

Years ago I was told you can replace CSV tables by simply replacing the CSV file. I figured this would also be true of a MyISAM file and it is. I use this perl script to replace MyISAM tables forcast and current observation weather data. The processing and tables are created on another computer. Weather forecasting is CPU and database expensive. I then copy (rsync) the files to the production system and run this script.

#!/usr/bin/perl
################################################################################
################################################################################
# mg_hot_replace_table.pl - Hot Replace a MySQL table.
#
# 2010-05-01 …

I guess many of you know, that using SELECT count(*) FROM table is problematic and slow when using Innodb tables.
This actually only applies to COUNT(*) queries without WHERE a clause as mentioned in the MySQL Performance Blog.

But if you got some slow count query in your application the best way to increase its performance is to replace / remove it.

So if you are going do to "SELECT count(*) FROM products" the best way, is to have a separated table
that stores the number of products. If you're inserting a row increment the counter, if you're deleting a row, decrement it.

Here is some example:
CREATE TABLE counter( number_of_products int(10) DEFAULT '0' NOT NULL);

Increment when you're adding a new product to the products table:

PLAIN TEXT SQL:

1. UPDATE counter SET …