Introduction to Physical DB
by K. Yue
1. Introduction
Example:
An article on flash memory and database lists important characteristics of flash memory for database consideration:
Physical Structures:
2. Indexing
MySQL Indexes
Example:
The optional index clause:
{INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option] ...
or
{FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ...
and
index_col_name: col_name [(length)] [ASC | DESC]
index_type: USING {BTREE | HASH}
index_option: KEY_BLOCK_SIZE [=] value | index_type | WITH PARSER parser_name | COMMENT 'string'
Example:
Full text search in MySQL: functions.
In Sakila, we have two tables: film and film_text, in which film_text has full indexing:
CREATE TABLE film_text (
film_id SMALLINT NOT NULL,
title VARCHAR(255) NOT NULL,
description TEXT,
PRIMARY KEY (film_id),
FULLTEXT KEY idx_title_description (title,description)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
To search for a film that contains the keyword "teacher" without using full text search.
select film_id, title, description
from film
where description like '%teacher%';
Possible problems:
Using full text indexing and searching:
select MATCH(title,description) AGAINST('teacher') AS score, film_id, title, description
from film
where MATCH(title,description) AGAINST('teacher')
order BY score DESC;
results in:
ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes
The correct way:
select MATCH(title,description) AGAINST('teacher') AS score, film_id, title, description
from film_text
where MATCH(title,description) AGAINST('teacher')
order BY score DESC;
Can you guess the algorithm of MySQL used to calculate scores from the results of the query above?
Example: Using AdventureWorks, a small data warehouse example created by Microsoft.
-- using AdventureWorks
-- Basic information about AdventureWorks.address.
use AdventureWorks;
desc address;
select count(*) from address;
-- Q1
select distinct AddressLine1, City, PostalCode
from address
order by PostalCode
limit 300;
-- Q2
select distinct AddressLine1, City, PostalCode
from address
where PostalCode = 98055;
-- Q3
select distinct AddressLine1, City, PostalCode
from address
where PostalCode = 98055 or PostalCode = 83402;
-- Q4
select distinct AddressLine1, City, PostalCode
from address
where PostalCode between 90000 and 92000
limit 300;
-- create index
create index postal_index on address(postalCode);
desc address;
-- Q1
select distinct AddressLine1, City, PostalCode
from address
order by PostalCode
limit 300;
-- Q2
select distinct AddressLine1, City, PostalCode
from address
where PostalCode = 98055;
-- Q3
select distinct AddressLine1, City, PostalCode
from address
where PostalCode = 98055 or PostalCode = 83402;
-- Q4
select distinct AddressLine1, City, PostalCode
from address
where PostalCode between 90000 and 92000
limit 300;
-- clean up
drop index postal_index on address;
desc address;