Dotaz: Zrychleni ipac-ng: indexy pro MySQL

[Ondřej Čečák]

Dobry den,

resim problem pomaleho zpracovani dat pro ip accounting programem ipac-ng. V uzivatelskem listu jsem dostal radu, abych zkusil pouzit indexy, ktere jsou v dokumentaci. Zel jejich nastaveni je pro postgresql a v mysql nefunguje.

Mohl by mi nekdo, prosim, pomoci s prevodem syntaxe tak, abych indexy mohl pouzit i pod MySQL?

Kod postgresql:

CREATE TABLE "logs" (
        "rule_name" character varying(32) NOT NULL,
        "bytes" bigint NOT NULL,
        "pkts" bigint NOT NULL,
        "hostname" character varying(16),
        "that_time" integer NOT NULL
);

REVOKE ALL on "logs" from PUBLIC;
GRANT ALL on "logs" to "postgres";
GRANT ALL on "logs" to "ipac";

CREATE  INDEX "logs_rule" on "logs" using btree ( "rule_name" "varchar_ops" );
CREATE UNIQUE INDEX "logs_rule_time" on "logs" using btree ( "rule_name" "varchar_ops", "that_time" "int4_ops" );
CREATE  INDEX "logs_time" on "logs" using btree ( "that_time" "int4_ops" );

Kod mysql:

CREATE TABLE logs (
  that_time bigint(20) NOT NULL default '0',
  rule_name varchar(128) NOT NULL default '',
  bytes bigint(20) default NULL,
  pkts bigint(20) default NULL,
  hostname varchar(64) default NULL,
  PRIMARY KEY  (that_time,rule_name)
) TYPE=MyISAM;

Predem diky!

Odpovědi

[Michal Čihař]

Stejné indexy vytvoříš asi takhle (nezkoušel jsem to):

ALTER TABLE logs ADD INDEX(rule_name);
ALTER TABLE logs ADD INDEX(that_time);

[Ondřej Čečák]

SQL prikazy probehly v pohode, zel zrychleni neproblehlo. V phpMyAdminu vidim jako mocnost (cardinallity)jako zadnou (none). Muze to byt chyba nebo to je vlastnost? :) (mimo to vidim varovani, ze sloupec that_time by nemel byt soucasne v primary a index klici)

V listu ipac-ng se mi na to dostalo odpovedi toto:

I think the combined index over "rule_name" and "that_time" is then missing. Isn't that possible to do with MySQL.

BTW: mozna to take muze byt proste jenom chyba ipac-ng.

[Michal Čihař]

Ale tenhle index tam je jako primární klíč...