HOBALL 筆記簿

February 27, 2014

Easy installation of MySQL in Linux Mint 15

Filed under: Linux Mint — Tags: , — hoball @ 3:07 am

The Chemical Linux

The recommended database client for database novices is MySQL. Being a light database, MySQL is easy to use and configure. Also, distributions such as Debian-based Linux Mint already have packages ready to be installed from the default repository. In this post, let me demonstrate how I installed MySQL:

Steps taken to install MySQL in Linux Mint:

1. Download MySQL client:
bash> apt-get install mysql-client

2. Download MySQL server:
bash> apt-get install mysql-server
Note: During installation of mysql-server, you will prompted to enter the root password. It is recommended to set it up.

3. Test if installation is successful using the following commands:
bash> /usr/bin/mysqladmin version
bash> /usr/bin/mysqladmin variables
Note: You should see an output showing details regarding mysql

4. Upon installation of mysql, a database named ‘test’ is already created by default. Test connection by connecting to the database
bash> mysql -u test
mysql>
You can now connect to the…

View original post 119 more words

March 12, 2010

ip2long vs INET_ATON

Filed under: PHP, SQL — Tags: , , , — hoball @ 9:15 pm

Source: http://mwillis.co.uk/mysql/ip2long-vs-inet_aton/

PHP ip2long() sometimes return negative integers,
while MySQL INET_ATON() function returns only positive numbers.

Ensure PHP ip2long() returns only positive intergers:

sprintf("%u", ip2long("254.254.254.254"));
$ip = ip2long($ip_address);
if ($ip < 0){ $ip += 4294967296; }

October 24, 2009

MySQL Memory Usage

Filed under: SQL — Tags: , , , — hoball @ 10:25 am

For tuning MySQL variables,

max_memory_needed = core_mysql + global_values + (thread_buffers * max_connections)

max_memory_needed = core_mysql
+ key_buffer_size
+ innodb_buffer_pool_size
+ innnodb_additional_memory_pool_size
+ innodb_log_buffer_size
+ max_tmp_tables * min(tmp_table_size,max_heap_table_size)
+ query_cache_size
+ 3 * myisam_sort_buffer_size
+ max_connections * ( read_buffer_size + join_buffer_size + read_rnd_buffer_size + thread_stack + (2 * max_packet_size) ))

Source:
http://www.pythian.com/news/1455/mysql-memory-consumption
http://www.mysqlperformanceblog.com/2006/05/17/mysql-server-memory-usage/

July 16, 2009

MySQL Select with JOIN

Filed under: SQL — Tags: , , — hoball @ 6:20 pm

找回從前的提問,現記錄如下:

我想請教各位一個SQL Statement的寫法:
現在的db內,有兩個tables,裏面的data是相連的。
categories
——————
category_id
category_name
categories_relationship
—————————-
category_id
parent_id
例如:
Parent:
category_id = 1, category_name = Games, parent_id=0
category_id = 2, category_name = Newspaper, parent_id=0
Subcategory:
category_id = 20, category_name = RPG, parent_id = 1
category_id = 21, category_name = Appledaily, parent_id=2
在一個php內,我得到某category_id,
現在想用SQL把: category name 和 它的parent name 顯示出來
我知道可以用兩句SQL來達成,但是我想知道一句就可以完成的寫法是怎樣的
請問會是用幾個JOIN,還是會create temp table?

作者: icomefromhk    時間: 2009-1-19 19:59
select sc.category_name, pc.category_name from categories sc join categories_relationship cr on sc.category_id = cr.category_id join categories pc on pc.parent_id = cr.category_id


作者: icomefromhk    時間: 2009-1-19 20:02
其實你轉用一個table裝晒d parent-child relationship都得
即係
categories
——————
category_id
category_name
parent_id
咁樣用一個join就已經拎倒你要既野

 

另外,想請問一般在什麼請況下才會把data 和relationship分開?

作者: icomefromhk    時間: 2009-1-19 23:10
data modeling既野好難講…不過你呢個情況, "一般黎講", 除非category同subcategory要裝既information係唔同, 如果唔係, 用同一個table黎裝"應該"對query既performance好d


作者: alextamly    時間: 2009-1-19 23:40
多數單對單o既就唔好分table喇..單對多的話就睇情況…

July 15, 2009

MySQL Database 設計

Filed under: SQL — Tags: , , — hoball @ 10:54 pm

1月時我在HKEPC的發問得到回應,現記錄如下:

無忌 發表於 2009-1-13 17:38

– 唔好放D同個table本身無關既野, column應該盡可能少
– 將兩個相關table既primary key放在另一個table, 唔好因為唔想咁多table而將好多Id放入同一個table之中, 咁會破壞大家既relationship, 增加存取時既難度, 通常兩三個已足夠.
– 唔好因為速度而放棄架構. 速度可以用唔同方法改善.

最基本設計例如:

Entries
——–
entry_id
title
content

Domains
——–
domain_id
url

DomainsEntries
—————
domain_id
(many) entry_id

如果怕Entries table變得大, 你可以用hashtable將佢既分成多個.

不過有樣野要講你知….我Database差D肥佬, 亦無database相關工作經驗, 意見只作參考 😉

MySQL selecting a random row efficiently

Filed under: SQL — Tags: , — hoball @ 10:49 pm

Here are the homework I made today, for the quest for an efficient way to get a random row from a MySQL database:
source 1 : http://akinas.com/pages/en/blog/mysql_random_row/

$offset_result = mysql_query( ” SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM `table` “);
$offset_row = mysql_fetch_object( $offset_result );
$offset = $offset_row->offset;
$result = mysql_query( ” SELECT * FROM `table` LIMIT $offset, 1 ” );

A reply by Pascal

Solution 4 took 1 minute on a table with 598420 rows.
I ended up doing this in PHP (table is static at this point)
$r = rand(1,598420);
$sql = “SELECT * FROM table WHERE id = $r”;
Takes 0.01 secs.

source 2 : http://www.dasprids.de/blog/2008/06/07/fetching-random-rows-of-mysql-efficiently

source 3 : http://poeticcode.wordpress.com/2007/12/31/mysql-select-random-row-very-efficient/

December 3, 2008

MySQL: MyISAM or InnoDB?

Filed under: SQL — Tags: , , — hoball @ 4:48 pm

From Here

Why you would use MyISAM:
– If you need to make use of “full text search”, although no prebuilt application will use FTS as MyISAM is the only engine to support it.
– The application is mostly for reporting purposes, data is loaded at off hour times in batch mode, as opposed to users coming in and updating frequently.
– SEcurity is not an issue. MyISAM stores all data in flat files that are easily read.
– Native hot backup and check utilities work for this engine since it is MySQL’s proprietery engine.

Why you would use InnoDB:
– Row level locking allows for multiple rows/data sets to be updated at a time without causing problems with the system.
– Enforcement of foreign keys, the only engine that works with MySQL that enforces them.
– uses “data files” similar to the larger DB’s such as SQL Server and Oracle, these are encrypted and therefore more secure.
– purchasing hot backup utility provides a license to use it – your company is theoretically covered if something bad happens.

November 3, 2008

MySQL – Primary Key, Unique, Index

Filed under: SQL — Tags: , , , — hoball @ 1:06 pm

Not really a formal definition, but here it is:

Primary Key – Key to the specific record, tells you what uniquely identifies that record
Index – one of more fields indexed to improve access time
Unique Index – type of index, means that a field or group of field is unique, so no other record can have the same one

Note that “primary” is called PRIMARY KEY not INDEX.
KEY is something on the logical level, describes your table and database design (i.e. enforces referential integrity …)
INDEX is something on the physical level, helps improve access time for table operations.
Behind every PK there is (usually) unique index created (automatically).

July 30, 2008

MySQL Import large database using commands

Filed under: SQL — Tags: , , , , — hoball @ 11:50 am

If you have a very large database, it is frustrating to restore/import it using HTTP as most probably there are transfer limit.
So, it is time to use the command prompt. Firstly, upload the sql file to an easily accessed folder(Windows/Linux). Then run the command:

mysql -u xxx -p -h SERVER_PATH DATABASE_NAME < file.sql

If the database is not created, you can create it first, by using:

CREATE DATABASE database_name;

July 23, 2008

MySQL – Show Tables in a Database

Filed under: SQL — Tags: , , — hoball @ 2:17 pm

To show all the tables in a selected database (currently working):

SHOW TABLES;

To show all the tables in ANOTHER database:

SHOW TABLES IN other_database;

To show a table who you forget the exact name, you may use the wildcard(%):

SHOW TABLES IN other_database LIKE “%salary%;

Blog at WordPress.com.