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
–
PC: oS 11.3 64 bit | Intel Core2 Quad Q8300@2.50GHz | KDE 4.6.2 | GeForce
9600 GT | 4GB Ram
Eee PC 1201n: oS 11.4 64 bit | Intel Atom 330@1.60GHz | KDE 4.6.0 | nVidia
ION | 3GB Ram
>
> 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.
–
PC: oS 11.3 64 bit | Intel Core2 Quad Q8300@2.50GHz | KDE 4.6.3 | GeForce
9600 GT | 4GB Ram
Eee PC 1201n: oS 11.4 64 bit | Intel Atom 330@1.60GHz | KDE 4.6.0 | nVidia
ION | 3GB Ram
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.
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.
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.
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);