RootForum Community

Code: Select all

Handler_read_rnd_next  	4,25 G 

Anzahl der Anfragen, die nächste Zeile in der Daten-Datei zu lesen. Dieser Wert wird hoch sein, wenn Sie viele Tabellen-Scans durchführen. Im Allgemeinen weist das darauf hin, dass Ihre Tabellen nicht korrekt indiziert sind, oder dass Ihre Anfragen nicht so geschrieben sind, dass Sie Vorteile aus den Indexen ziehen, die Sie haben.

Code: Select all

Handler_read_rnd_next  	4,25 G

Anzahl der Anfragen, die nächste Zeile in der Daten-Datei zu lesen. Dieser Wert wird hoch sein, wenn Sie viele Tabellen-Scans durchführen. Im Allgemeinen weist das darauf hin, dass Ihre Tabellen nicht korrekt indiziert sind, oder dass Ihre Anfragen nicht so geschrieben sind, dass Sie Vorteile aus den Indexen ziehen, die Sie haben.

Code: Select all

root@localhost [(none)]> show status like "%trig%";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Com_show_triggers | 0     |
+-------------------+-------+
1 row in set (0.00 sec)

Code: Select all

mysql> EXPLAIN SELECT * FROM news_news WHERE (title REGEXP 'liege|liege' OR main_text REGEXP 'liege|liege' OR input_1 REGEXP 'liege|liege' OR input_2 REGEXP 'liege|liege' OR input_3 REGEXP 'liege|liege' OR input_4 REGEXP 'liege|liege') AND kat_id='100' ORDER BY date DESC;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

Code: Select all

+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

Code: Select all

SELECT * 
FROM news_news 
WHERE (title REGEXP 'liege|liege' OR main_text REGEXP 'liege|liege' OR input_1 REGEXP 'liege|liege' OR input_2 REGEXP 'liege|liege' OR input_3 REGEXP 'liege|liege' OR input_4 REGEXP 'liege|liege') AND kat_id='100' ORDER BY date DESC;

Code: Select all

# mysql-instance /export/data/rootforum 5.0.17 3340
Creating 5.0.17 instance
  from mysql-max-5.0.17-linux-i686-glibc23/
  in /export/data/rootforum,
  listening on port 3340
# mysqlstart-3340
# mysql-3340 -u root
root@localhost [none]> create database greenrover;
root@localhost [none]> use greenrover;
root@localhost [greenrover]> create table t (
  `id` serial,
  `title` text NOT NULL,
  `main_text` text NOT NULL,
  `input_1` text NOT NULL,
  `input_2` text NOT NULL,
  `input_3` text NOT NULL,
  `input_4` text NOT NULL,
  `kat_id` int(10) unsigned NOT NULL,
);

Code: Select all

kris@linux:~> cat greenrover.pl
#! /usr/bin/perl --

use DBI;
use DBD::mysql;

my $dsn = "DBI:mysql:database=greenrover;host=127.0.0.1;port=3340";
my $dbh = DBI->connect($dsn, "root");
$dbh->{RaiseError} = 1;

my $rows = 1300;      # Zeilen in der Tabelle
my $fieldlen = 1300;  # Mindestlänge der Textfelder

# Beispielworte für Einträge
open IN, "</usr/share/dict/words" or die;
@words = <IN>;
close IN;
chomp @words;

$words = $#words;

for (my $i=0; $i<$rows; $i++) {
  # Kram in Tabelle t reinscheppern
  my $sth = $dbh->prepare("insert into t ( id, kat_id, title, main_text, input_1, input_2, input_3, input_4 ) values ( NULL, ?, ?, ?, ?, ?, ?, ?)");

  # 20% aller Einträge sind kat_id = 100, der Rest ist Random
  $sth->bind_param(1, rand()<0.2?100:rand()*1000);

  # Baue Einträge aus wirren Worten
  for (my $j=2; $j<=7; $j++) {
    my $str = "";
    while (length($str) < $fieldlen) {
      $str .= $words[rand*$words];
    }
    $sth->bind_param($j, $str);
  }

  # Schuß!
  $sth->execute();
}

Code: Select all

root@localhost [greenrover]> analyze table t;
+--------------+---------+----------+-----------------------------+
| Table        | Op      | Msg_type | Msg_text                    |
+--------------+---------+----------+-----------------------------+
| greenrover.t | analyze | status   | Table is already up to date |
+--------------+---------+----------+-----------------------------+
1 row in set (0.00 sec)
root@localhost [greenrover]> show table status like "t"G
*************************** 1. row ***************************
           Name: t
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 1300
 Avg_row_length: 8103
    Data_length: 10535136
Max_data_length: 281474976710655
   Index_length: 11276288
      Data_free: 0
 Auto_increment: 6613
    Create_time: 2006-02-01 20:21:22
    Update_time: 2006-02-01 20:21:22
     Check_time: 2006-02-01 20:21:30
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

Code: Select all

root@localhost [greenrover]> select id 
 from t 
where title regexp 'melissa|melissa' 
   or main_text regexp 'melissa|melissa' 
   or input_1 regexp  'melissa|melissa' 
   or input_2 regexp 'melissa|melissa' 
   or input_3 regexp 'melissa|melissa' 
   or input_4 regexp 'melissa|melissa';
+------+
| id   |
+------+
| 5313 |
| 5464 |
| 5542 |
| 5729 |
| 5911 |
| 6156 |
| 6168 |
| 6296 |
| 6538 |
| 6553 |
| 6588 |
| 6610 |
+------+
12 rows in set (3.58 sec)
root@localhost [greenrover]> explain select id 
 from t 
where title regexp 'melissa|melissa' 
   or main_text regexp 'melissa|melissa' 
   or input_1 regexp  'melissa|melissa' 
   or input_2 regexp 'melissa|melissa' 
   or input_3 regexp 'melissa|melissa' 
   or input_4 regexp 'melissa|melissa'G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1300
        Extra: Using where
1 row in set (0.00 sec)

Code: Select all

root@localhost [greenrover]> alter table t add index (kat_id);
Query OK, 1300 rows affected (7.91 sec)
Records: 1300  Duplicates: 0  Warnings: 0

Code: Select all

root@localhost [greenrover]> select id 
 from t 
where kat_id = 100 
and ( 
    title regexp 'melissa|melissa' 
 or main_text regexp 'melissa|melissa' 
 or input_1 regexp 'melissa|melissa' 
 or input_2 regexp 'melissa|melissa' 
 or input_3 regexp 'melissa|melissa' 
 or input_4 regexp 'melissa|melissa' 
);
+------+
| id   |
+------+
| 5729 |
| 5911 |
| 6553 |
| 6588 |
+------+
4 rows in set (0.71 sec)

Code: Select all

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: ref
possible_keys: kat_id
          key: kat_id
      key_len: 4
          ref: const
         rows: 260
        Extra: Using where
1 row in set (0.00 sec)

root@localhost [greenrover]> select 3.58/0.71 as speedup G
*************************** 1. row ***************************
speedup: 5.042254
1 row in set (0.00 sec)

Code: Select all

root@localhost [greenrover]> alter table t add fulltext f (title, main_text, input_1, input_2, input_3, input_4 );
Query OK, 1300 rows affected (17.23 sec)
Records: 1300  Duplicates: 0  Warnings: 0
root@localhost [greenrover]> show table status like "t"G
*************************** 1. row ***************************
           Name: t
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 1300
 Avg_row_length: 8103
    Data_length: 10535136
Max_data_length: 281474976710655
   Index_length: 22517760
      Data_free: 0
 Auto_increment: 6613
    Create_time: 2006-02-01 20:38:42
    Update_time: 2006-02-01 20:38:42
     Check_time: 2006-02-01 20:39:00
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

Code: Select all

root@localhost [greenrover]> select id, match(title, main_text, input_1, input_2, input_3, input_4) against ('melissa') as rank 
 from t 
where kat_id = 100 
  and match(title, main_text, input_1, input_2, input_3, input_4) against ('melissa');
+------+------------------+
| id   | rank             |
+------+------------------+
| 5729 | 0.81813311576843 |
| 5911 | 0.48297706246376 |
| 6553 | 0.48286464810371 |
| 6588 | 0.48263877630234 |
+------+------------------+
4 rows in set (0.00 sec)

Code: Select all

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: fulltext
possible_keys: kat_id,f
          key: f
      key_len: 0
          ref:
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

Code: Select all

root@localhost [greenrover]> CREATE TABLE `tt` (
  `id` serial,
  `kat_id` int(10) unsigned NOT NULL,
  KEY `kat_id` (`kat_id`)
);
root@localhost [greenrover]> CREATE TABLE `tt_txt` (
  `id` bigint(20) unsigned NOT NULL,
  `type` int(10) unsigned NOT NULL,
  `txt` text NOT NULL,
  PRIMARY KEY  (`id`,`type`)
);
root@localhost [greenrover]> insert into tt select id, kat_id from t;
Query OK, 1300 rows affected (0.03 sec)
Records: 1300  Duplicates: 0  Warnings: 0

root@localhost [greenrover]> insert into tt_txt 
select id, 1, input_1 from t 
union 
select id, 2, input_2 from t 
union 
select id, 3, input_3 from t 
union 
select id, 4, input_4 from t 
union 
select id, 5, title from t 
union 
select id, 6, main_text from t;
Query OK, 7800 rows affected (0.84 sec)
Records: 7800  Duplicates: 0  Warnings: 0

Code: Select all

root@localhost [greenrover]> select distinct tt.id 
 from tt join tt_txt on tt.id = tt_txt.id 
where tt.kat_id = 100 
  and tt_txt.txt regexp 'melissa';
+------+
| id   |
+------+
| 5729 |
| 5911 |
| 6553 |
| 6588 |
+------+
4 rows in set (0.36 sec)

Code: Select all

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tt
         type: ref
possible_keys: id,kat_id
          key: kat_id
      key_len: 4
          ref: const
         rows: 260
        Extra: Using temporary
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: tt_txt
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: greenrover.tt.id
         rows: 6
        Extra: Using where; Distinct
2 rows in set (0.01 sec)

Code: Select all

root@localhost [greenrover]> alter table tt_txt add fulltext f (txt);
Query OK, 7800 rows affected (9.37 sec)
Records: 7800  Duplicates: 0  Warnings: 0
root@localhost [greenrover]> show table status like "tt_txt"G
*************************** 1. row ***************************
           Name: tt_txt
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 7800
 Avg_row_length: 1365
    Data_length: 10648904
Max_data_length: 281474976710655
   Index_length: 13569024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2006-02-01 20:56:42
    Update_time: 2006-02-01 20:56:43
     Check_time: 2006-02-01 20:56:52
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)
root@localhost [greenrover]> select distinct tt.id from tt join tt_txt on tt.id = tt_txt.id where kat_id = 100 and match(tt_txt.txt) against('melissa');
+------+
| id   |
+------+
| 5729 |
| 6553 |
| 6588 |
| 5911 |
+------+
4 rows in set (0.00 sec)

root@localhost [greenrover]> explain select distinct tt.id from tt join tt_txt on tt.id = tt_txt.id where kat_id = 100 and match(tt_txt.txt) against('melissa')G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tt_txt
         type: fulltext
possible_keys: PRIMARY,f
          key: f
      key_len: 0
          ref:
         rows: 1
        Extra: Using where; Using temporary
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: tt
         type: eq_ref
possible_keys: id,kat_id
          key: id
      key_len: 8
          ref: greenrover.tt_txt.id
         rows: 1
        Extra: Using where
2 rows in set (0.00 sec)