MySQL Relations Problem

mostafaxxx · May 7, 2011, 9:01pm

Hello every one,

I’m having a problem in setting up a relation between two tables

the first table (the parent) : visitors_data


+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
|** visitor_id**  | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| visitor_ip  | varchar(50)      | YES  |     | NULL    |                |
| access_date | datetime         | YES  |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+

the secound table (the child) : messages_data


+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| message_id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| **visitor_id**    | int(10) unsigned | NO   |     | NULL    |                |
| visitor_name  | varchar(50)      | YES  |     | NULL    |                |
| visitor_email | varchar(50)      | YES  |     | NULL    |                |
| message_date  | datetime         | YES  |     | NULL    |                |
| message_txt   | varchar(255)     | YES  |     | NULL    |                |
+---------------+------------------+------+-----+---------+----------------+

note that the two tables are empty

now i’d like to add a relation to bind messages_data.visitor to visitors_data.visitor_id
i’ve tried the following command

mysql> ALERT TABLE messages_data
    -> ADD FOREIGN KEY (visitor_id) REFERENCES visitors_data (visitor_id)
    -> ON DELETE CASCADE;

but i always get the following error

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALERT TABLE messages_data
ADD FOREIGN KEY (visitor_id) REFERENCES visitors_data ' at line 1

here is the out put of mysqladmin version :

mysqladmin  Ver 8.42 Distrib 5.1.53, for suse-linux-gnu on i686
Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version		5.1.53-log
Protocol version	10
Connection		Localhost via UNIX socket
UNIX socket		/var/run/mysql/mysql.sock
Uptime:			3 hours 15 min 57 sec

Threads: 1  Questions: 22  Slow queries: 0  Opens: 20  Flush tables: 1  Open tables: 10  Queries per second avg: 0.1

martin_helm · May 7, 2011, 9:37pm

mostafaxxx wrote:

> ALERT TABLE

ALTER TABLE

mostafaxxx · May 8, 2011, 7:25pm

hahaha :shame:

by the way when i set a column as primary key does that make it index key too

martin_helm · May 8, 2011, 7:44pm

mostafaxxx wrote:

>
> hahaha :shame:
>
> by the way when i set a column as primary key does that make it index
> key too
>
I do not know the answer, because I do not use mysql, but as far as I can
see you can show the indexes in mysql with the command


SHOW INDEX FROM <table name>

so you can easily verify with it if it is indexed or not.

Monex · May 9, 2011, 8:57pm

Hi,

martin_helm is right, in MySQL key is a synonym for index. If you use MyISAM as storage engine the primary key is a unique index/key but if you use InnoDB it is the primary key is a clustered Index/key. You should keep this in mind when using InnoDB.

Hope this helps

djh-novell · May 10, 2011, 12:24pm

Monex wrote:
> martin_helm is right, in MySQL key is a synonym for index. If you use
> MyISAM as storage engine the primary key is a unique index/key but if
> you use InnoDB it is the primary key is a clustered Index/key. You
> should keep this in mind when using InnoDB.

What is that you think needs to be kept in mind?

Monex · May 12, 2011, 8:43pm

Hi,

I mean the cluster index. The clustered index can have performance advantages if you use it the right way but also it could slowdown the whole table access. The problem is when the clustered index gets fragmented (due to many almost empty pages) the whole access will slow down. Then the only possibility is to recreate the table (which is done automatic by optimize tables).

One good thing to avoid this is using an auto increment value as clustered index. This should avoid creating new pages which aren’t filled completely as when it happen on random primary key inserts. Of course it is not possible to avoid this completely.

A good resource MySQL Performance Blog for getting informations around InnoDB and MySQL.

Hope this helps

djh-novell · May 13, 2011, 11:56am

Monex wrote:
> … The problem is when the clustered index gets
> fragmented (due to many almost empty pages) the whole access will slow
> down. …

Ah, thanks for that.

> One good thing to avoid this is using an auto increment value as
> clustered index. …

That’s lucky, then. I always do!

> A good resource ‘MySQL Performance Blog’
> (http://www.mysqlperformanceblog.com/) for getting informations around
> InnoDB and MySQL.

Thanks for the link.

Cheers, Dave

imterpsfan2 · May 14, 2011, 2:14am

A clustered index is preferred for primary key, auto-increment fields. This type of index stores the data on disk in the same order as your primary key. A clustered index guarantees the order of your data. Generally, you can only specify one clustered index per table but many non-clustered index. Non-clustered index are generally for columns in joins, columns in where clauses, and columns in order by and where clauses.

In short, a clustered index designates a “physical ordering” of data. A non-clustered index specifies a “logical ordering” of data.

In any RDBMS I have used, when you designate a primary key you are also creating an index on that table, usually of the clustered kind. I like to explicitly create the primary key by an alter table statement, that way I can specify a name for the index instead of some system-generated index name.

ALTER TABLE employees
ADD CONSTRAINT employees_pk PRIMARY KEY (employee_id);

vodoo · May 25, 2011, 2:47pm

@mostafaxxx

What table type are you using? MyISAM?