Wie MySQL-Abfrage optimieren? (große Tabelle, Dauer: 0.2s)

[Matze]

Hallo,

ich habe 2 MySQL-Tabellen, die wie folgt aufgebaut sind:

SQL-Code:

CREATE TABLE `log_parser_ips` (
  `ip_from` INTEGER(10) UNSIGNED NOT NULL DEFAULT '0',
  `ip_to` INTEGER(10) UNSIGNED NOT NULL DEFAULT '0',
  `cc` CHAR(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`ip_from`),
  UNIQUE KEY `ip_to` (`ip_to`),
  UNIQUE KEY `ip_from` (`ip_from`),
  KEY `cc` (`cc`)
)ENGINE=MyISAM
ROW_FORMAT=FIXED CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci';

SQL-Code:

CREATE TABLE `log_parser_countries` (
  `cc` CHAR(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `country` VARCHAR(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`cc`)
)ENGINE=MyISAM
CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci';

Die Tabelle "log_parser_ips" enthält die IP-Adressenbereiche (ip_from / ip_to) und ordnet diese einem Ländercode (cc) zu.
Die Tabelle ""log_parser_countries" ordnet den Ländercodes (cc) den Ländernamen (country) zu.

Anhand der IP ermittel ich das Land folgendermaßen:

SQL-Code:

SELECT c.country, c.cc FROM log_parser_countries c
   JOIN log_parser_ips i
      ON i.cc = c.cc
   WHERE i.ip_from <= 123456789 AND i.ip_to >= 123456789
   LIMIT 1

Wenn ich die Abfrage ohne "JOIN" ausführe:

SQL-Code:

SELECT c.country, c.cc FROM log_parser_countries c, log_parser_ips i
   WHERE i.ip_from <= 123456789
      AND i.ip_to >= 123456789
      AND i.cc = c.cc
   LIMIT 1

ändert sich nichts, was die Ausführungszeit betrifft. Diese liegt durchschnittlich bei stolzen 0.2 Sekunden.

Wie kann ich das optimieren?

Ich habe schon überlegt, anstelle der Spalte "cc" (CHAR) für die Zuordnung eine Integer-Spalte zu verwenden. Aber ob das so viel bringt, weiß ich nicht.

Grüße, Matze

[Bernhard Geyer]

Zitat von Matze:

Wie kann ich das optimieren?

1, Verwendung von Prepared Statements (und damit zwangsweis Parametrisierte Abfragen)
2, Verwendung von Stored Procedures

[Matze]

Hallo Bernhard,

danke für deine Antwort.
Ich habe mich mal in die Prepared Statements eingearbeitet, doch ändert sich die Ausführungszeit leider nicht.

Mein PHP-Code schaut so aus:

Code:

$this->[color=#018801]db_connection[/color] = [b][color=#0000ff]new[/color][/b] [color=#018801]mysqli[/color]($conf_db[[color=#df0000]'host'[/color]], $conf_db[[color=#df0000]'user'[/color]], $conf_db[[color=#df0000]'passwd'[/color]], $conf_db[[color=#df0000]'name'[/color]]);
[b][color=#0000ff]if[/color][/b] ([color=#018801]mysqli_connect_errno[/color]())
   [b][color=#0000ff]die[/color][/b]([color=#018801]mysqli_connect_error[/color]() . [color=#df0000]' - '[/color] . [color=#018801]mysqli_connect_errno[/color]());

$this->[color=#018801]db_statement[/color] = $this->[color=#018801]db_connection[/color]->[color=#018801]prepare[/color]([color=#df0000]"SELECT c.country, c.cc FROM log_parser_countries c
   JOIN log_parser_ips i
      ON i.cc = c.cc
   WHERE i.ip_from <= ? AND i.ip_to >= ?
   LIMIT 1"[/color]);
[b][color=#0000ff]if[/color][/b] (!$this->[color=#018801]db_statement[/color])
   [b][color=#0000ff]echo[/color][/b] $this->[color=#018801]db_connection[/color]->[color=#018801]error[/color];

$this->[color=#018801]db_statement[/color]->[color=#018801]bind_param[/color]([color=#df0000]"ii"[/color], $ip, $ip);
[b][color=#0000ff]if[/color][/b] (!$this->[color=#018801]db_statement[/color]->[color=#018801]execute[/color]())
   [b][color=#0000ff]echo[/color][/b] $this->[color=#018801]db_statement[/color]->[color=#018801]error[/color];
   
$this->[color=#018801]db_statement[/color]->[color=#018801]bind_result[/color]($country, $cc);

[b][color=#0000ff]if[/color][/b] (!$this->[color=#018801]db_statement[/color]->[color=#018801]fetch[/color]())
   [b][color=#0000ff]echo[/color][/b] [color=#df0000]'empty mysql result'[/color];

$this->[color=#018801]db_connection[/color]->[color=#018801]close[/color]();

Grüße, Matze


PS: Falls sich das nun zu einer PHP-Frage entwickelt, verschiebe ich das Thema.

[DeddyH]

Zitat:

SQL-Code:

UNIQUE KEY `ip_to` (`ip_to`),
  UNIQUE KEY `ip_from` (`ip_from`),

Bist Du sicher, dass das so stimmt? Ich vermute, Du wolltest eine eindeutige Kombination der beiden IPs, oder?

[Daniel]

Hast Du mal ein Explain-Select darauf losgelassen? Damit könntest Du weitere Indizien auf den Flaschenhals bekommen.
(Einfach das Keyword "EXPLAIN" vor das Statement schreiben und dann in phpMyAdmin oder wo auch immer ausführen.)

[mquadrat]

Ich schließe mich DeddyH an. Ein kombinierter Index würde wohl besser funktionieren

[Matze]

Hi,

@Deddy: Hm ich bin mir nicht so ganz sicher, aber da die Zahlen nicht doppelt vorkommen, habe ich UNIQUE gesetzt.
@Daniel: EXPLAIN kannte ich bisher gar nicht. Interessantes Feature, doch mit dem Ergebnis fange ich leider nichts an, was mich bzgl. Performance weiterbringen könnte:

Code:

id    select_txpe   table   type     possible_keys              key       ken_len   ref       rows   Extra
----------------------------------------------------------------------------------------------------------------
1     SIMPLE        i       range    PRIMARY,ip_to,ip_from,cc   PRIMARY   4         NULL      27     Using where
1     SIMPLE        c       eq_ref   PRIMARY                    PRIMARY   6         db.i.cc   1

@mquadrat: Meinst du sowas "PRIMARY KEY (`ip_to`, `ip_from`)"?
Falls ja, auch da merke ich keinen Unterschied.

Grüße, Matze

[mquadrat]

Das Explain verrät dir in diesem Beispiel, dass er für die Bereichsprüfung die Indizes PRIMARY, ip_to, ip_from und cc einsetzen könnte, sich aber für den PRIMARY entscheidet. Dabei findet er 27 Einträge, die dann mit einem where auf einen reduziert werden.

[Matze]

Hi mquadrat,

ok, aber was sagt mir das nun genau? Dass die anderen Indizes überflüssig sind?
Oder kann ich irgendwas herauslesen, damit ich die Abfrage beschleunigen kann?

Grüße, Matze

[mquadrat]

Wenn das die einzige Query auf die Tabelle ist, dann kannst du die anderen Indizes tatsächlich entfernen. Hat aber eher Einfluß auf die Schreib-Performance. Mittels Explain kann man seine Indizes "tunen". Je weniger Datensätze durch den Index durchkommen, desto besser. Bei nem kombinierten müsste da eigentlich statt der 27 eine 1 stehen..

Hast du davon irgendwo einen Dump, damit man das mal ausprobieren kann?