MySQL: Abfrage zu langsam

[brainyy]

Hi,
brauch mal eure Hilfe.

Muss hier zwei Abfragen auf zwei Verschiedene Datenbanken machen.
Die eine Datenbank hat ~750.000 Einträge, die andere 5.

Hier mal die Abfragen:

authorize_check_query = "SELECT id,UserName,Attribute,Value,op FROM ${authcheck_table} WHERE country = 'ok' AND INET_ATON('${callingid}') BETWEEN start_ip AND end_ip LIMIT 1"

 authorize_reply_query = "SELECT R.id,R.UserName,R.Attribute,R.Value,R.op FROM ${authreply_table} AS R, ${authcheck_table} AS C WHERE R.UserName = C.UserName AND INET_ATON('${callingid}') BETWEEN C.start_ip AND C.end_ip ORDER BY R.id"

Mit 4,2 Sekunden dauert die Antwort (deutlich) zu lange..

 

[heddesheimer]

Setz mal jeweils einen Index für die Spalten, die in deiner WHERE Klausel vorkommen. Im ersten Fall wären das country und INET_ATON. Das sollte die Antwortzeit auf wenige Millisekunden drücken.

Gruß Marian

 

[brainyy]

Moin,

ich habe das gerade mal mit dem Index probiert.
Zum testen habe ich erstmal die "country" Spalte aus der Abfrage entfernt.
Index liegt jetzt auf "start_ip" und "end_ip". (Das INET_ATON rechnet nur eine IP Adresse in einen int Wert um.)

Leider ist die Antwortszeit nur um 0,6 Sekunden kleiner geworden.

 

[Nico Graichen]

Hi,

Du könntest ja mal noch deine Tabellen posten, vielleicht gibt's da ja auch noch optimierungsmöglichkeiten.
Was aber ein Grund sein kann, dass es nicht viel schneller wird ist die umrechnung der IP-Adressen.

 

[brainyy]

Hi,
die authcheck_table hat folgende Spalten:

id int(11),
UserName varchar(64),
Attribute (32),
op char(2),
Value varchar(253),
start_ip bigint(10),
end_ip bigint(10),
country char(2)

authreply_table:

id int(11),
UserName varchar(64),
Attribute (32),
op char(2),
Value varchar(253)

 

[heddesheimer]

(Das INET_ATON rechnet nur eine IP Adresse in einen int Wert um.)

Das wird dein Problem sein. Wenn du die abzufragende Spalte erst berechnen musst, kann MySQL den Index nicht verwenden.

Gruß Marian

 

[brainyy]

Also gespeichert sind die Ip Adresse in der Tabelle als int.
Macht mySQL das nicht so, dass er die erst umrechnet und dann vergleicht?
Sollte das dann nicht mit Index gehen?

Hier nochmal, was ich getestet habe:

mysql> SELECT id,UserName,Attribute,Value,op FROM checkip WHERE INET_ATON("193.159.240.44") BETWEEN start_ip AND end_ip LIMIT 1;

1 row in set (1.34 sec)

mysql> SELECT R.id,R.UserName,R.Attribute,R.Value,R.op FROM radreply AS R, checkip AS C WHERE R.UserName = C.UserName AND INET_ATON("193.159.240.44") BETWEEN C.start_ip AND C.end_ip ORDER BY R.id;

2 rows in set (2.50 sec)


Gibt es da sonst noch eine Möglichkeit das ganze zu beschleunigen?

 

[heddesheimer]

Kannst du den Wert aus INET_ATON() nicht einfach in eine eigene Spalte speichern? Datentyp sollte INT UNSIGNED sein:

NOTE: When storing values generated by INET_ATON(), it is recommended that you use an INT UNSIGNED column. If you use a (signed) INT column, then values corresponding to IP addresses for which the first octet is greater than 127 will be truncated to 2147483647 (that is, the value returned by INET_ATON('127.255.255.255')). See Section 11.2, “Numeric Types”.

Gruß Marian

 

[brainyy]

Ja stimmt, das mit dem unsigned könnte ich nochmal probieren.

Kann die IP Adresse leider nicht so wegspeichern, denn ich muss überprüfen ob die IP Adresse zwischen "start_ip" und "end_ip" liegt. Wenn ich die noch aufdröseln würde, hätte ich anstatt 750.000 auf einmal 191.250.000 Einträge. (Mal ganz abgesehen vom Script aufwand..).

 

[heddesheimer]

Sorry, ich hatte deine Abfrage doch nicht so ganz gründlich gelesen. So wie du abfragst, müsstest du für start_ip und end_ip jeweils einen Index erzeugen um die Abfrage schneller zu bekommen.

Gruß Marian