Showing posts with label Table. Show all posts
Showing posts with label Table. Show all posts

Monday, May 24, 2010

MySQL: Many tables or many databases?

Question:
For a project we having a bunch of data that always have the same structure and is not linked together. There are two approaches to save the data:
* Creating a new database for every pool (about 15-25 tables)
* Creating all the tables in one database and differ the pools by table names.
Which one is easier and faster to handle for MySQL?

Answer:
There should be no significant performance difference between multiple tables in a single database versus multiple tables in separate databases.

In MySQL, databases (standard SQL uses the term "schema" for this) serve chiefly as a namespace for tables. A database has only a few attributes, e.g. the default character set and collation. And that usage of GRANT makes it convenient to control access privileges per database, but that has nothing to do with performance.

You can access tables in any database from a single connection (provided they are managed by the same instance of MySQL Server). You just have to qualify the table name:

SELECT * FROM database17.accounts_table;

This is purely a syntactical difference. It should have no effect on performance.

Regarding storage, you can't organize tables into a file-per-database as @Chris speculates. With the MyISAM storage engine, you always have a file per table. With the InnoDB storage engine, you either have a single set of storage files that amalgamate all tables, or else you have a file per table (this is configured for the whole MySQL server, not per database). In either case, there's no performance advantage or disadvantage to creating the tables in a single database versus many databases.

There aren't many MySQL configuration parameters that work per database. Most parameters that affect server performance are server-wide in scope.

Regarding backups, you can specify a subset of tables as arguments to the mysqldump command. It may be more convenient to back up logical sets of tables per database, without having to name all the tables on the command-line. But it should make no difference to performance, only convenience for you as you enter the backup command.

-- Bill Karwin (the author of SQL Antipatterns from Pragmatic Bookshelf)

Wednesday, February 03, 2010

collapsing margins of table

display=block的元素,垂直方向的collapsing margins会发生重合。
IE8/safari/chrome中table默认的display=table,其渲染时vertical margin会发生重合,与display=block的元素效果一样,但在firefox中测试其效果与float元素的效果一样,不会发生垂直方向的margin重合。
个人的解决方法是将table设置为float=left,并在其后设置一个clear=both的DIV,避免table的margin被重合。

关于collapsing margins详细说明可查看CSS21规范的8.3.1 Collapsing margins。
另外对于一个maring:10px的DIV元素,如果此元素没有内容,并没有设置padding和border值,其上部的margin与其下部的margin也会重合,也就是此DIV在垂直方向上只会占据10px的高度。如果此DIV的top或者bottom与别的元素发生过重合,则其本身的top和bottom margin不会再重合。
规范原文说明如下:
If the top and bottom margins of a box are adjoining, then it is possible for margins to collapse through it. In this case, the position of the element depends on its relationship with the other elements whose margins are being collapsed.
If the element’s margins are collapsed with its parent’s top margin, the top border edge of the box is defined to be the same as the parent’s.
Otherwise, either the element’s parent is not taking part in the margin collapsing, or only the parent’s bottom margin is involved. The position of the element’s top border edge is the same as it would have been if the element had a non-zero top border.

Monday, August 11, 2008

Integrate table with tooltip based prototype

var Tooltip = Class.create();
Tooltip.prototype = {
initialize: function(element, cid) {
var options = Object.extend({
defaultCss: true,
margin: "0px",
padding: "5px",
backgroundColor: "#FBFBF3",
border: "#EAE6C4 1px solid",
width: "300px",
deltaX: 5,
deltaY: 5,
zindex: 1000,
opacity: 0.8
}, arguments[2] || {});
this.options = options;
this.element = element;

var tooltipContent = arguments[3] || Table.cells[cid] || " ";
new Insertion.Bottom(this.element,
"<div id='" + cid + "' style='display:none;word-break:break-all;width:" + this.options.width + "'>" + tooltipContent + "</div>");
this.tooltip = $(cid);

this.displayEvent = this.showTooltip.bindAsEventListener(this);
this.hideEvent = this.hideTooltip.bindAsEventListener(this);
this.registerEvents();
},

registerEvents: function() {
Event.observe(this.element, "mouseover", this.displayEvent);
Event.observe(this.element, "mouseout", this.hideEvent);
},

showTooltip: function(event){
Event.stop(event);
var mouseX = Event.pointerX(event);
var mouseY = Event.pointerY(event) + this.options.deltaY;
var halfWidth = this.getBodyWidth() / 2;
if (mouseX > halfWidth) {
mouseX -= this.tooltip.getWidth() + this.options.deltaX;
} else {
mouseX += this.options.deltaX;
}

this.setStyles(mouseX, mouseY);
new Element.show(this.tooltip);
},

setStyles: function(x, y) {
Element.setStyle(this.tooltip, {
position: 'absolute',
top: y + "px",
left: x + "px",
zindex: this.options.zindex
});

if (this.options.defaultCss){
Element.setStyle(this.tooltip, {
margin: this.options.margin,
padding: this.options.padding,
backgroundColor: this.options.backgroundColor,
zindex: this.options.zindex,
border: this.options.border,
width: this.options.width,
opacity: this.options.opacity,
filter: 'alpha(opacity=' + this.options.opacity * 100 + ')'
});
}
},

hideTooltip: function(event){
new Element.hide(this.tooltip);
},

getBodyWidth: function(){
return document.body.clientWidth;
}

}


var Table = {
rowEvenClass: 'roweven',
rowOddClass: 'rowodd',
rawCellContent: true,
heads: {},
rows: {},
cells: {},
getBodyRows: function(table) {
var table = $(table);
var id = table.id ? table.id : table.id = "table" + $$("table").indexOf(table);
if(!Table.rows[id] && table.tBodies[0]) {
Table.rows[id] = (table.tHead && table.tHead.rows.length > 0) ? $A(table.tBodies[0].rows): $A(table.tBodies[0].rows);
}
return Table.rows[id] || [];
},
getHeadCells: function(table) {
var table = $(table);
var id = table.id ||
var id = table.id ? table.id : table.id = "table" + $$("table").indexOf(table);
if(!Table.heads[id]) {
Table.heads[id] = $A((table.tHead && table.tHead.rows.length > 0) ? table.tHead.rows[table.tHead.rows.length-1].cells: table.rows[0].cells);
}
return Table.heads[id];
},
getCellIndex: function(cell) {
var cell = $(cell);
return $A(cell.parentNode.cells).indexOf(cell);
},
getRowIndex: function(row) {
var row = $(row);
return $A(row.parentNode.rows).indexOf(row);
},
getCellText: function(cell) {
if(!cell) { return ""; }
var cell = $(cell);
textContent = cell.textContent ? cell.textContent: cell.innerText;
return textContent;
}
}

Table.Rows = {
stripe: function(table) {
var table = $(table);
var rows = Table.getBodyRows(table);
rows.each(function(row, index) {
Table.Rows.addStripeClass(table, row, index);
Table.Rows.truncateCells(table, row, index);
});
},
addStripeClass: function(table, row, index) {
table = table || row.up('table');
var css = ((index + 1) % 2 === 0 ? Table.rowEvenClass: Table.rowOddClass);
var cn = row.className.split(/\s+/);
var newCn = [];
for(var x = 0, l = cn.length; x < l; x ++) {
if(cn[x] !== Table.rowEvenClass && cn[x] !== Table.rowOddClass) { newCn.push(cn[x]); }
}
newCn.push(css);
row.className = newCn.join(" ");
},
truncateCells: function(table, row, rowIndex) {
table = table || row.up('table');
$A(row.cells).each(function(cell, cellIndex) {
var cid = table.id + "_row" + rowIndex + "_col" + cellIndex;
var cidTruncated = cid + "_truncated";
if (Table.rawCellContent) {
Table.cells[cid] = cell.innerHTML;
} else {
Table.cells[cid] = Table.getCellText(cell);
}
Table.cells[cidTruncated] = false;
try {
Table.Cells.truncateTextNodes(cell, cidTruncated);
} catch (e) {
// ignore
}
if (Table.cells[cidTruncated]) new Tooltip(cell, cid);
});
}
};

Table.Cells = {
truncate: function(node, cidTruncated) {
var nodeData = node.data;
if (/[\u4e00-\u9fa5]/.test(nodeData)) {
if (nodeData.strip().length > 20) {
node.data = nodeData.truncate(20);
if (!Table.cells[cidTruncated]) Table.cells[cidTruncated] = true;
}
} else {
if (nodeData.length > 40) {
node.data = nodeData.truncate(40);
if (!Table.cells[cidTruncated]) Table.cells[cidTruncated] = true;
}
}
},
truncateTextNodes: function(cell, cidTruncated) {
$A(cell.childNodes).each(function(node){
if (node.nodeType == 3) {
Table.Cells.truncate(node, cidTruncated);
} else if (node.nodeType == 1 && ["select", "script", "embed", "object", "textarea", "table", "iframe"].include(node.tagName.toLowerCase())) {
Table.cells[cidTruncated] = false;
throw new SyntaxError("Do not truncate " + node.tagName);
} else {
Table.Cells.truncateTextNodes(node, cidTruncated);
}
});
}
}

Table.load = function() {
$$("table").each(function(table){
Table.Rows.stripe(table);
});
}

Event.observe(window, 'load', Table.load);

Reference:
http://www.millstream.com.au/view/code/tablekit/
http://tooltip.crtx.org/

Friday, July 04, 2008

9.2.2. 识别符大小写敏感性[转自mysql5 中文手册]

在MySQL中,数据库对应数据目录中的目录。数据库中的每个表至少对应数据库目录中的一个文件(也可能是多个,取决于存储引擎)。因此,所使用操作系统的大小写敏感性决定了数据库名和表名的大小写敏感性。这说明在大多数Unix中数据库名和表名对大小写敏感,而在Windows中对大小写不敏感。一个显著的例外情况是Mac OS X,它基于Unix但使用默认文件系统类型(HFS+),对大小写不敏感。然而,Mac OS X也支持UFS卷,该卷对大小写敏感,就像Unix一样。

注释:尽管在某些平台中数据库名和表名对大小写不敏感,不应在同一查询中使用不同的大小写来引用给定的数据库或表。下面的查询不会工作,因为它同时引用了表my_tables和as MY_tables:

mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1;
列、索引、存储子程序和触发器名在任何平台上对大小写不敏感,列的别名也不敏感。

默认情况,表别名在Unix中对大小写敏感,但在Windows或Mac OS X中对大小写不敏感。下面的查询在Unix中不会工作,因为它同时引用了别名a和A:

mysql> SELECT col_name FROM tbl_name AS a
-> WHERE a.col_name = 1 OR A.col_name = 2;
然而,该查询在Windows中是可以的。要想避免出现差别,最好采用一致的转换,例如总是用小写创建并引用数据库名和表名。在大多数移植和使用中建议使用该转换。

在MySQL中如何在硬盘上保存和使用表名和数据库名由lower-case-table-names系统变量确定,可以在启动mysqld时设置。lower-case-table-names可以采用下面的任一值:

0:
使用CREATE TABLE或CREATE DATABASE语句指定的大写和小写在硬盘上保存表名和数据库名。名称比较对大小写敏感。在Unix系统中的默认设置即如此。请注意如果在大小写不敏感的文件系统上用--lower-case-table-names=0强制设为0,并且使用不同的大小写访问MyISAM表名,会导致索引破坏。

1:
表名在硬盘上以小写保存,名称比较对大小写敏感。MySQL将所有表名转换为小写以便存储和查找。该行为也适合数据库名和表的别名。该值为Windows和Mac OS X系统中的默认值。

2:
表名和数据库名在硬盘上使用CREATE TABLE或CREATE DATABASE语句指定的大小写进行保存,但MySQL将它们转换为小写以便查找。名称比较对大小写敏感。注释:只在对大小写不敏感的文件系统上适用! InnoDB表名以小写保存,例如lower-case-table-names=1。

在Windows和Mac OS X中,lower-case-table-names的 默认值是1。

提示,手册中lower-case-table-names拼写有误,以此文为准。

Thursday, September 27, 2007

[1062]MySQL::Error: Duplicate entry '0' for key 4' on query

数据同步机制发生崩溃,错误提示为 Duplicate entry '0' for key 4' on query,类似错误以前也见过,但一般是因为Unique的索引重复了才报错,这次的错误中提示的0这个Unique Id是不可能有的,主键是自增的id,不会有0出现,猜是对应的表或者表索引文件出问题了,用check table table_name检查了一下,果然有一个警告,二个报错,用rapire table table_name修复,由于表比较大,修复了很旧还是没好,最后拿上次的备份回来重新同步了半天才恢复正常。

Thursday, May 10, 2007

Dump a table to YAML dump

from DZone Snippets: rails code by rohan_kini (ROhan Kini)


//Dump a table to YAML dump


require 'yaml'

task :dump_fixtures => :environment do |t|
File.open('dump.yml', "w") do |f|
js.each do |j|
f.print YAML.dump ({"record_#{j.id}" => j.attributes})
end
end
end

Saturday, April 07, 2007

Pros and Cons of MySQL Table Types

By John W. Horn PhD

Of all the positive things that MySQL brings to the table, probably the most overlooked is multiple table types. This facet of the application is overlooked as a feature and more importantly is overlooked at design time.

MySQL has six distinct table types.

* MyISAM
* MERGE
* ISAM
* HEAP
* InnoDB
* BDB or BerkeleyDB Tables

Finding a transaction table that's just my type

Two of these table types currently support transactions. Transactions give the user greater control when working with data. You would use syntax similar to the following for a manual transaction.

START TRANSACTION;

SELECT @A:=SUM(salary) FROM table1 WHERE type=1;

UPDATE table2 SET summmary=@A WHERE type=1;

COMMIT;

Of the two commonly used transaction table types, the first is BerkeleyDB transactional tables provided by SleepyCat (www.sleepycat.com). In order to use BDB tables use a binary with BDB support or configure the source with the withberkeleydb option. If you don't want to use BDB tables, start the MySQL server with the skipbdb option. This will save a lot of memory, since the BDB library won't be included. However, you won't be able to use BDB tables. BDB is not used nearly as much as our second alternative which is InnoDB. InnoDB features rowlevel locking, consistent nonlocking read in SELECTs and common tablespace for all tables.

InnoDB Tables are made by Innobase Oy (www.innodb.com), and are distributed under the GPL as well as commercially. If you need commercial support or commercial licenses for your application and cost is a concern, not using InnoDB will save you about 20-50 % for licenses and support contracts. If data integrity is a concern InnoDB provides MySQL with a transactional storage engine and crash recovery capabilities. InnoDB has been designed for maximum performance when processing large data volumes and any other diskbased relational database engine does probably not match CPU efficiency. There are other transactional table types out there (such as Gemini), but they do not seem to be used any more than BDB. So, in a nutshell, most users prefer the speed and features of InnoDB.
A Database is no fun when you are locked out

The default table type for MySQL is MyISAM. It has table level locking, which means during an UPDATE, nobody can access any other record of the same table. BDB uses Page level locking, and during an UPDATE, nobody can access any other record residing in the same database page of that table, until the locking transaction issues a COMMIT.

InnoDB however, uses Row level locking. Row level locking ensures that during an UPDATE, nobody can access that particular row, until the locking transaction issues a COMMIT. Any of the above table types will probably be fine for a web server, but in a LAN application can cause unecessary issues.
Special circumstances call for special tools (or tables)

MERGE tables are a collection of identical MyISAM tables that can be used as one. You can only SELECT, DELETE, and UPDATE from the collection of tables. If you DROP the MERGE table, you are only dropping the MERGE specification. One reasons why you would use MERGE tables is to get more speed. You can split a big read-only table and then put the different table parts on different disks. You could do more efficient searches. If you know exactly what you are looking for, you can search in just one of the split tables for some queries and use a MERGE table for others. Repairs are more efficient. It's easier to repair the individual files that are mapped to a MERGE file than trying to repair a really big file. MyISAM and therefore MERGE tables are represented as individual files on the harddrive. You can go around the file-size limit for the operating system.

Some of the disadvantages of using MERGE tables are:

* You can only use identical MyISAM tables for a MERGE table.
* REPLACE doesn't work.
* Key reads are slower.

Also, you can't do DROP TABLE, ALTER TABLE, DELETE FROM table_name without a WHERE clause, REPAIR TABLE, TRUNCATE TABLE, OPTIMIZE TABLE, or ANALYZE TABLE on any of the table that is mapped by a MERGE table that is "open". If you do this, the MERGE table may still refer to the original table and you will get unexpected results. The easiest way to get around this deficiency is to issue the FLUSH TABLES command, ensuring no MERGE tables remain "open".

Well, that should make you think twice about using MERGE tables. ISAM tables will disappear in MySQL version 5.0, so it wouldn't be a good idea to use them. Last but not least is the HEAP table type. HEAP tables use hashed indexes and are stored in memory. This makes them very fast, but if MySQL crashes you will lose all data stored in them. They are very useful for temporary tables. HEAP sounds cool but I don't think the risk justifies the performance.
The Lowdown on MySQL Table Types

Most people use MyISAM if they need speed and InnoDB for data integrity. You can use more than one or any combination of these table types in your database. Remember to asses the needs of your application before building it. Even though MyISAM is faster than InnoDB in the MySQL world, InnoDB is fast compared to any database engine. With InnoDB you get transactions, speed and integrity three features not usually used in the same sentence. Most of my customers want as much speed as they can get, but at the end of the day, good data integrity lets them sleep at night.
About the Author

John W. Horn PhD has been programming since 1983 and is currently the CEO of Interstate Software the only MySQL training center in North America. His company is the only "Gold Level" MySQL training, support and consulting group in the world. He is based in Kansas City Missouri.