MySQL Forums
Forum List  »  Performance

Re: Queries slow down daily, optimizing helps -> why?
Posted by: Kaspar Con
Date: February 26, 2009 02:30AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
2086
March 18, 2009 03:58AM
Re: Queries slow down daily, optimizing helps -> why?
2402
February 26, 2009 02:30AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.