some index for mysql table

[rukhem]

V 1.6.1

some field in sql query hasn`t index, you can see it if include log-queries-not-using-indexes option in my.cnf file.

I create some index for faster work mysql.
I think it`s realy good.

CREATE INDEX graphid ON graphs_items ( graphid );
CREATE INDEX sortorder ON graphs_items ( sortorder );

CREATE INDEX screenxy ON screens_items ( screenid,x,y );

CREATE INDEX userid ON users_groups ( userid );
CREATE INDEX usrgrpid ON users_groups ( usrgrpid );

CREATE INDEX eventid ON escalations ( eventid );
CREATE INDEX status ON escalations ( status );
CREATE INDEX nextcheck ON escalations ( nextcheck );

CREATE INDEX nodetype ON nodes ( nodetype );


CREATE INDEX hostid ON hosts_groups ( hostid );
CREATE INDEX groupid ON hosts_groups ( groupid );
CREATE INDEX id ON rights ( id );
CREATE INDEX name ON nodes ( name );

==========================================
Why?
==========================================

#SELECT gi.* FROM graphs_items gi WHERE gi.graphid=464 ORDER BY gi.sortorder, gi.itemid DESC;

CREATE INDEX graphid ON graphs_items ( graphid );
CREATE INDEX sortorder ON graphs_items ( sortorder );


#select * from screens_items where screenid=6 and x=5 and y=4;

CREATE INDEX screenxy ON screens_items ( screenid,x,y );

#SELECT MAX(g.gui_access) as gui_access
#FROM usrgrp g, users_groups ug
#WHERE ug.userid=2 AND g.usrgrpid=ug.usrgrpid;

CREATE INDEX userid ON users_groups ( userid );
CREATE INDEX usrgrpid ON users_groups ( usrgrpid );


#SELECT e.escalationid,e.actionid,e.r_eventid,e.esc_step,e .status, #ev.eventid,ev.source,ev.object,ev.objectid,ev.clo ck,ev.value,ev.acknowledged
#FORM escalations e,events ev
#WHERE e.eventid=ev.eventid and e.status in (0,1) and e.nextcheck<=1230197156 and escalationid between 00 and 09;

CREATE INDEX eventid ON escalations ( eventid );
CREATE INDEX status ON escalations ( status );
CREATE INDEX nextcheck ON escalations ( nextcheck );


#SELECT * FROM nodes WHERE nodetype=1 order by nodeid;

CREATE INDEX nodetype ON nodes ( nodetype );


#SELECT DISTINCT n.nodeid, n.name as node_name, h.hostid, h.host, min(r.permission) as permission, ug.userid
#FROM hosts h
#LEFT JOIN hosts_groups hg ON hg.hostid=h.hostid
#LEFT JOIN groups g ON g.groupid=hg.groupid
#LEFT JOIN rights r ON r.id=g.groupid
#LEFT JOIN users_groups ug ON ug.usrgrpid=r.groupid and ug.userid=2
#LEFT JOIN nodes n ON (h.hostid div 100000000000000)=n.nodeid
#WHERE (h.hostid div 100000000000000) in (0)
#GROUP BY h.hostid,n.nodeid,n.name,h.host,ug.userid
#ORDER BY n.name,n.nodeid, h.host, permission, ug.userid;


#hosts_groups
#hostid,groupid - may be not need? ( KEY `hosts_groups_groups_1` (`hostid`,`groupid`))
CREATE INDEX hostid ON hosts_groups ( hostid );
CREATE INDEX groupid ON hosts_groups ( groupid );

# rights
# filed: id
CREATE INDEX id ON rights ( id );

#nodes
# filed: name
CREATE INDEX name ON nodes ( name );

[bradek]

Any official reviews or opinions regarding these optimizations?

[exkg]

Aly ? Alexei ?


Some words about this ?
We can create this indexes (and test) in a hard use ZABBIX database if needed.



[]s,
Luciano

[Alexei]

I am not happy about making any further changes in 1.6.x. Majority of SQL queries will be reviewed in 1.8, so some of the mentioned indexes won't be required.

However some of the proposed indexes will be added to 1.8. We are aware of certain queries using full table scans. It will be fixed.

[xs-]

could you pass us some hints on which index will certainly make 1.8? I wouldnt mind some speed improvements here and there (perhaps this will help with syncing nodes )

[Aly]

Aly ? Alexei ?


Some words about this ?
We can create this indexes (and test) in a hard use ZABBIX database if needed.



[]s,
Luciano

First of all I haven't reviewedl mentioned queries, so this is based on my experience of previous optimizations..

Let's take a look at very first mentioned query:
#SELECT gi.* FROM graphs_items gi WHERE gi.graphid=464 ORDER BY gi.sortorder, gi.itemid DESC;

CREATE INDEX graphid ON graphs_items ( graphid );
CREATE INDEX sortorder ON graphs_items ( sortorder );

Well index graphid already exists..
Index on sortorder is useless, why? because mysql uses only one index per query, and here is more effective is graphid, it's easier select 10 rows from 1000000 by that index and then sort thous 10 rows, then select it by full scan and then sort 10 rows by index.

In many queries there is that balance, where you have to choose what is more effective, selecting or sorting. Best way is to make such query where it uses same index in selecting and sorting.

And more.. Or primary DB is MySQL, but we should remember that there are PGSQL and ORACLE, at least sometimes