Yes, I really like to ;-)
Create Tables:
CREATE TABLE `person` (
`id` int(11) NOT NULL auto_increment,
`user_id` int(11) default NULL,
`vorname` tinytext collate utf8_unicode_ci,
`nachname` tinytext collate utf8_unicode_ci,
`titel` tinytext collate utf8_unicode_ci,
`telefon` tinytext collate utf8_unicode_ci,
`matrikelnummer` text collate utf8_unicode_ci,
`email` tinytext collate utf8_unicode_ci,
`buero` tinytext collate utf8_unicode_ci,
`dummy` set('0','1') collate utf8_unicode_ci NOT NULL default '0',
`handy` tinytext collate utf8_unicode_ci,
`webseite` tinytext collate utf8_unicode_ci,
`adressverzeichnis` set('0','1') collate utf8_unicode_ci default '1',
`lehre` set('0','1') collate utf8_unicode_ci default '0',
`alt_id` int(11) default NULL,
`kurz_cv` text collate utf8_unicode_ci,
`lehrgebiete` text collate utf8_unicode_ci,
`forschungsinteressen` text collate utf8_unicode_ci,
`praxisprojekte` text collate utf8_unicode_ci,
`publikationen` text collate utf8_unicode_ci,
PRIMARY KEY (`id`),
UNIQUE KEY `fk_user` (`user_id`),
KEY `adressverzeichnis` (`adressverzeichnis`),
KEY `matrikelnummer` (`matrikelnummer`(5)),
KEY `lehre` (`lehre`),
KEY `email` (`email`(5))
) ENGINE=MyISAM AUTO_INCREMENT=12164 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL auto_increment,
`un` varchar(64) collate utf8_unicode_ci NOT NULL default '',
`pw` varchar(64) collate utf8_unicode_ci NOT NULL,
`zeitstempel` double NOT NULL default '0',
`typ` smallint(6) NOT NULL default '0',
`logcount` int(11) NOT NULL default '0',
`newsletter_check` int(11) NOT NULL default '0',
`recover_pw` tinyint(4) NOT NULL default '0',
`hash` varchar(64) collate utf8_unicode_ci NOT NULL,
`letzte_rolle_id` int(11) default NULL,
`archiv_id` int(11) default NULL,
`rss` varchar(64) collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `un` (`un`),
UNIQUE KEY `rss` (`rss`),
KEY `id_letzte_rolle` (`letzte_rolle_id`),
KEY `fk_archiv` (`archiv_id`)
) ENGINE=MyISAM AUTO_INCREMENT=11482 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Users and global privileges'
(BTW: I know that we should create an extra Table personExtra with all the thext fields into it, but this is something different that doesn't affect this problem, as the table is the same, right?)
Show Index:
mysql> SHOW INDEX FROM user;
+-------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| user | 0 | PRIMARY | 1 | id | A | 10799 | NULL | NULL | | BTREE | |
| user | 0 | un | 1 | un | A | 10799 | NULL | NULL | | BTREE | |
| user | 0 | rss | 1 | rss | A | 10799 | NULL | NULL | | BTREE | |
| user | 1 | id_letzte_rolle | 1 | letzte_rolle_id | A | 10799 | NULL | NULL | YES | BTREE | |
| user | 1 | fk_archiv | 1 | archiv_id | A | 10799 | NULL | NULL | YES | BTREE | |
+-------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.00 sec)
mysql> SHOW INDEX FROM person;
+--------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| person | 0 | PRIMARY | 1 | id | A | 11390 | NULL | NULL | | BTREE | |
| person | 0 | fk_user | 1 | user_id | A | 11390 | NULL | NULL | YES | BTREE | |
| person | 1 | adressverzeichnis | 1 | adressverzeichnis | A | 1 | NULL | NULL | YES | BTREE | |
| person | 1 | matrikelnummer | 1 | matrikelnummer | A | 2278 | 5 | NULL | YES | BTREE | |
| person | 1 | lehre | 1 | lehre | A | 1 | NULL | NULL | YES | BTREE | |
| person | 1 | email | 1 | email | A | 5695 | 5 | NULL | YES | BTREE | |
+--------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
6 rows in set (0.00 sec)
SLOW:
mysql> EXPLAIN SELECT user.id FROM user, person WHERE user.id = person.user_id AND person.nachname like '%heg%';
+----+-------------+--------+-------+---------------+---------+---------+-------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+-------------------+-------+-------------+
| 1 | SIMPLE | user | index | PRIMARY | PRIMARY | 4 | NULL | 10799 | Using index |
| 1 | SIMPLE | person | ref | fk_user | fk_user | 5 | mystudy13.user.id | 1 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+-------------------+-------+-------------+
2 rows in set (0.00 sec)
FAST:
mysql> EXPLAIN SELECT user.id FROM user, person WHERE user.id = person.user_id AND person.nachname like '%heg%';
+----+-------------+--------+-------+---------------+---------+---------+-------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+-------------------+-------+-------------+
| 1 | SIMPLE | user | index | PRIMARY | PRIMARY | 4 | NULL | 10799 | Using index |
| 1 | SIMPLE | person | ref | fk_user | fk_user | 5 | mystudy13.user.id | 1 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+-------------------+-------+-------------+
2 rows in set (0.00 sec)
I hope you can find any hints.
Greetings,
Sami