Shantanu's Blog

Efficient full-text searchs on large sets of data

This is the answer to the question posted by one of the Experts on this blog.

http://community.livejournal.com/mysql/138390.html

If you want to search for a word in a given table, the best option is to use the 'Full Text Search' that is available only for the MyISAM tables.
As you can see from the following statements, I can search for the word "sham" without using Full Text indexes.
You will have to add the Stored procedure available here...

http://forge.mysql.com/tools/tool.php?id=204

The only statement that needs explanation is the call for the stored procedure. Here is how to use the procedure to explode almost any table to your advantage.

CALL mysql.normalize_table('test.Employee', 'id', 'Name', ' ');

"mysql" can be replaced with the DB Name where the procedure is stored.
"normalize_table" is the name of the procedure that you can download from URL mentioned above.
"test.Employee" is the table to be split.
"id" is the Unique key of that table (usually auto incremented primary key)
"Name" is the column name that needs to be exploded.
Space ' ' is the delimiter.

_____


mysql>drop table if exists mysql.SplitValues;
Query OK, 0 rows affected (0.00 sec)

mysql>CREATE TABLE mysql.SplitValues (cid varchar(40), value varchar(500) ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)

mysql>drop table if exists test.Employee;
Query OK, 0 rows affected (0.00 sec)

mysql>CREATE TABLE test.Employee (
`id` int(11) default NULL,
`Name` varchar(50) NOT NULL,
`PhoneNo` varchar(15) default 'Unknown Phone',
PRIMARY KEY (`id`)
);
Query OK, 0 rows affected (0.03 sec)

mysql>INSERT INTO test.Employee VALUES (1, 'Manohar Ram','12345');
Query OK, 1 row affected (0.00 sec)

mysql>INSERT INTO test.Employee VALUES (2, 'Manohar1 sham','12345');
Query OK, 1 row affected (0.00 sec)

mysql>select * from test.Employee;
+----+---------------+---------+
| id | Name | PhoneNo |
+----+---------------+---------+
| 1 | Manohar Ram | 12345 |
| 2 | Manohar1 sham | 12345 |
+----+---------------+---------+
2 rows in set (0.00 sec)

mysql>CALL mysql.normalize_table('test.Employee', 'id', 'Name', ' ');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>select * from mysql.SplitValues;
+------+----------+---------------------+
| cid | value | dateadded |
+------+----------+---------------------+
| 1 | Manohar | 2010-10-27 08:18:56 |
| 1 | Ram | 2010-10-27 08:18:56 |
| 2 | Manohar1 | 2010-10-27 08:18:56 |
| 2 | sham | 2010-10-27 08:18:56 |
+------+----------+---------------------+
4 rows in set (0.00 sec)

mysql>alter table SplitValues add key (value, cid);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql>select a.* from test.Employee as a inner join mysql.SplitValues as b on a.id = b.cid where b.value = 'sham';
+----+---------------+---------+
| id | Name | PhoneNo |
+----+---------------+---------+
| 2 | Manohar1 sham | 12345 |
+----+---------------+---------+
1 row in set (0.00 sec)

(08:22) mysql>explain select a.* from test.Employee as a inner join mysql.SplitValues as b on a.id = b.cid where b.value = 'sham';
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+--------------------------+
| 1 | SIMPLE | b | ref | value | value | 503 | const | 1 | Using where; Using index |
| 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | mysql.b.cid | 1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+--------------------------+
2 rows in set (0.01 sec)

Labels: mysql case study, mysql tips

1) Foreign keys are used to restrict the data being inserted unless the parent record is created. Isn't it logical that the child can not be born unless the parents are born? Therefore when I try to create the child table first, I get an error 1005 - (Can't create table).
2) While I am inserting the records, I have to follow the same sequence.
Therefore the first insert into child table fails with error no 1452 - (Cannot add or update a child row: a foreign key constraint fails) since no corresponding record is present in the parent table. Once I insert a record in parent table, my child table will allow it as well.
3) When I am trying to drop both the tables, I need to drop the child first and then the parent. Or else I get an error 1217 - (Cannot delete or update a parent row)

# Remember, the child has to reference a column to parent and not the other way round.

create table child(
userID int not null,
FOREIGN KEY (userID) REFERENCES parent(ID),
primary key (userID)
) ENGINE=InnoDB;
ERROR 1005 (HY000): Can't create table './test/child.frm' (errno: 150)

CREATE TABLE parent(
`ID` int(11) default NULL,
`name` varchar(100) default NULL,
`city` varchar(100) default NULL,
key (ID)
) ENGINE=InnoDB;
create table child(
userID int not null,
FOREIGN KEY (userID) REFERENCES parent(ID),
primary key (userID)
) ENGINE=InnoDB;

insert into child values (1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test/child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`userID`) REFERENCES `parent` (`ID`));

insert into parent values (1, 'abc', 'delhi');
insert into child values (1);

drop table if exists parent;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails;

drop table if exists child;
drop table if exists parent;

The same table can be a parent and a child. It's like a family tree. The grandpa has 2 sons and each son will have 1 or 2 children.

http://dev.mysql.com/downloads/workbench/

If you are using mysql workbench, you can easily create a graphical representation of the data structure. On the "Database" menu, there is a "Reverse Engineer" wizard. It will let you connect to a MySQL server and reverse engineer schema. The graph generated will show the "References:" and "Referenced By:" columns. "References" will show the parents of the current table and "Referenced By" will show its children. Parents are shown in green dotted line with the sing of ">". The child tables are linked with blue line.

Labels: mysql FAQ, mysql tips