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:

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?

[Matze]

Zitat:

Zitat von mquadrat

Bei nem kombinierten müsste da eigentlich statt der 27 eine 1 stehen..

Hm für mich sieht das nun rein optisch viel schlechter aus:

Code:

id    select_txpe   table   type     possible_keys              key       ken_len   ref       rows   Extra
----------------------------------------------------------------------------------------------------------------
1     SIMPLE        i       ALL      PRIMARY                    NULL      NULL      NULL      234
1     SIMPLE        c       ref      PRIMARY,cc                 cc        6         db.c.cc   385    Using where

Aber wieso das so ist. :gruebel:

Einen Dump habe ich leider keinen da.
Und das zu exportieren wäre etwas groß (vielleicht 4-5 MB).

[mquadrat]

Was haste denn geändert zwischen den zwei Explains? Jetzt benutzt er gar keinen Index mehr ;)

[Matze]

Die "log_parser_ips"-Tabelle sieht nun so aus:

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_to`, `ip_from`),
  KEY `cc` (`cc`)
)ENGINE=MyISAM
ROW_FORMAT=FIXED CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci';

Grüße

[mquadrat]

mhh dann arbeitet der mySQL Optimierer wohl anders, als ich dachte :D

Ich würde jetzt auch einfach nur noch hin- und herprobieren (geht ohne Daten halt schlecht).

[Matze]

Gezippt ist das alles schnuckelig klein, merke ich gerade. ;)

Wenn du herumtesten möchtest, gerne. :-)

[memphis]

Probier einen Index auf ip_from (eventuell auch auf ip_to) zu setzten:

Code:

KEY `ip_from` (`ip_from`)

[user0815]

Habe zwar auch keine Lösung aber ne Menge links hierzu (gegoogelt) gefunden:

http://countrylist.net/
kann man als MySQL Dump downloaden

http://www.wipmania.com/de/base/
download SQL-Format (zip,460kB)

http://www.maxmind.com/app/csv
http://www.rechner-support.com/t1741...-zuordnen.html

Für SQL Abfragen
http://www.heise.de/software/downloa..._browser/28899
Offizielle Seite:
http://dev.mysql.com/downloads/gui-tools/5.0.html
denke der sollte helfen.

Falls überflüssig einfach den Beitrag löschen.

[mquadrat]

:D

Das Ding arbeitet wie Firebird. Die Spaltenreihenfolge muss in Query und Index gleich sein.

SQL-Code:

Zeige Datensätze 0 - 0 (1 insgesamt, die Abfrage dauerte 0.0004 sek.)
SQL-Befehl:
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

mit Index auf ip_from, ip_to

[Matze]

Hallo

@user0815: Danke, doch eine Liste habe ich ja bereits und einen MySQL-Client auch. ;)
@mquadrat: Hm das klingt gut, aber irgendwie bekomme ich es nicht hin.

Meine Tabellen sehen nun so aus:

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 '',
  KEY `ips` (`ip_from`, `ip_to`)
)ENGINE=MyISAM
ROW_FORMAT=FIXED CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci';

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';

Hast du diese auch so oder anders?

Grüße, Matze

[user0815]

Deiner #1

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

Meiner, habe andere Tabellen genommen sowie Felder umbenannt.

SQL-Code:

SELECT c.Continent, c.IntName, c.Name FROM countrys_de c
JOIN t_ipbereich i
ON i.ISO2 = c.ISO2
WHERE i.ipfrom <= 123456789 AND i.ipto >= 123456789;

Also eigentlich kein Unterschied. Das Feld ISO2 ist in beiden Tabellen vom Typ varchar(2) und es liegt jeweils ein INDEX auf dem Feld ISO2.
Mit dem MySQL Query Browser braucht die Abfrage: 0,0009 s (0,0003) geholt
Mit phpMyAdmin: Zeige Datensätze 0 - 0 (1 insgesamt, die Abfrage dauerte 0.0005 sek.)

countrys_de = 249 Einträge
t_ipbereich = 58143 Einträge

[mquadrat]

@Matze

Ich hab dein .Sql File genommen und eingelesen. Danach hab ich nur den Index geändert.

SQL-Code:

--
-- Tabellenstruktur für Tabelle `log_parser_countries`
--

CREATE TABLE IF NOT EXISTS `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 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `log_parser_ips`
--

CREATE TABLE IF NOT EXISTS `log_parser_ips` (
  `ip_from` int(10) unsigned NOT NULL default '0',
  `ip_to` int(10) unsigned NOT NULL default '0',
  `cc` char(2) collate utf8_unicode_ci NOT NULL default '',
  PRIMARY KEY  (`ip_from`,`ip_to`),
  KEY `cc` (`cc`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=FIXED;

[Matze]

Hm komisch, so hatte ich es eigentlich auch.

PHP zeigt mir auch dann knapp 0.2 Sekunden an, seltsam. MySQLQueryBrowser nur 0.0046 Sekunden.
Dann dürfte letzteres wohl stimmen. :-)

Ich danke euch.

Grüße, Matze

[alzaimar]

Neulich kam ein ehamaliger Mitarbeiter mit einem Db-Problem zu mir: Windows, PHP und MySQL. War grottenlangsam. Ob ich mir seine DB-Struktur mal anschauen kann: Soweit alles super, 3NF, Indexe usw. Top. Trotzdem saulangsam...

... später hat er das unter Linux ausgeführt und da waren die Performanceprobleme auf einmal verschwunden.

Wir vermuten, das irgendwas in Win/PHP in Zusammenarbeit mit MySQL eine gewaltige Performancebremse ist.

[Matze]

Hallo,

das habe ich auch schon festgestellt. Unter Windows sind die Abfragen bei mir ebenfalls deutlich langsamer als auf dem Linux-Webserver.
Es wäre wirklich interessant zu wissen, woran das genau liegt.

Grüße, Matze

[mquadrat]

Ich wollte letzt testweise mal die Kombination IIS, FastCGI, PHP, mySQL ausprobieren und schauen ob die schneller ist, aber leider leider wie immer zu wenig Zeit für solche Spielerein