Midgard database indexes

A week ago Rambo wrote about MySQL optimization tricks. Today I decided to make detailed tests with database performance.

Idea

I use midgard_query_builder methods to produce SQL query, but the main point is to test database itself so all queries are executed in MySQL prompt as setting properties from MySQL resources is a bottleneck here.

$qb = new midgardquerybuilder("midgard_topic");

$qb->add_constraint('up', '<>', 0);

$qb->add_constraint('parameter.domain', '<>', '');

$qb->add_constraint('parameter.name', '<>', '');

$qb->add_constraint('metadata.created', '>', '2002-02-01 00:00:00');

$qb->add_constraint('metadata.published', '>', '2002-02-01 00:00:00');

Query:

SELECT topic.guid, topic.sitegroup, topic.metadata_creator, NULLIF(topic.metadata_created,'0000-00-00 00:00:00') AS metadata_created, topic.metadata_revisor, NULLIF(topic.metadata_revised,'0000-00-00 00:00:00') AS metadata_revised, topic.metadata_revision, topic.metadata_locker, NULLIF(topic.metadata_locked,'0000-00-00 00:00:00') AS metadata_locked, topic.metadata_approver, NULLIF(topic.metadata_approved,'0000-00-00 00:00:00') AS metadata_approved, topic.metadata_authors, topic.metadata_owner, NULLIF(topic.metadata_schedule_start,'0000-00-00 00:00:00') AS metadata_schedule_start, NULLIF(topic.metadata_schedule_end,'0000-00-00 00:00:00') AS metadata_schedule_end, topic.metadata_hidden, topic.metadata_nav_noentry, topic.metadata_size, topic.metadata_published, NULLIF(topic.metadata_exported,'0000-00-00 00:00:00') AS metadata_exported, NULLIF(topic.metadata_imported,'0000-00-00 00:00:00') AS metadata_imported,topic.metadata_deleted, topic.metadata_score, topic.up AS up,topic.id AS id, NULLIF(topic.created,'0000-00-00 00:00:00') AS created, topic.revisor AS revisor, topic.score AS score, topic.code AS code, topic.creator AS creator, NULLIF(topic.revised,'0000-00-00 00:00:00') AS revised, topic.name AS name, topic.revision AS revision, topic.owner AS owner, topic_i.title AS title, topic_i.lang AS lang, topic_i.description AS description, topic_i.sid AS sid, topic_i.extra AS extra FROM topic_i,topic,record_extension WHERE (topic.up <> 0 AND record_extension.domain <> '' AND record_extension.name <> '' AND topic.metadata_created > '2002-02-01 00:00:00' AND topic.metadata_published > '2002-02-01 00:00:00' AND topic.name <> '') AND topic.id=topic_i.sid AND topic_i.lang IN (0, 0) AND topic.guid = record_extension.parent_guid AND topic.guid = record_extension.parent_guid AND topic.sitegroup IN (0, 0) AND topic.metadata_deleted = FALSE;

Note that we use SQL standardized NULLIF control flow function. So on PHP level , never write code like :

if ( $object->metadata->created == '0000-00-00 00:00:00') 

but instead write it simpler and faster:

if ( $object->metadata->created) 

The test

MySQL Cache is off.
Query is executed 3 times every time when table is altered and I use average time.

Indexes on id and sitegroup.

* 13 rows in set (18.96 sec)

CREATE INDEX deleted_idx ON topic (metadata_deleted);

* 13 rows in set (19.00 sec)

CREATE INDEX name_idx ON topic (name);

* 13 rows in set (19.04 sec)

CREATE INDEX up_idx ON topic (up);

* 13 rows in set (19.06 sec)

CREATE INDEX metadata_created_idx ON topic (metadata_created);

* 13 rows in set (19.05 sec)

CREATE INDEX metadata_published_idx ON topic (metadata_published);

* 13 rows in set (19.12 sec)

CREATE INDEX record_extension_oid_idx ON record_extension (oid);

* 13 rows in set (19.04 sec)

CREATE INDEX record_extension_domain_idx on record_extension (domain(255));

* 13 rows in set (19.04 sec)

CREATE INDEX value_idx ON record_extension (value(255));

* 13 rows in set (19.05 sec)

CREATE INDEX name_idx ON record_extension (name(255));

* 13 rows in set (19.02 sec)

CREATE INDEX parent_guid_idx ON record_extension (parent_guid(80));

* 13 rows in set (0.01 sec) (!!)

CREATE INDEX tablename_idx ON record_extension (tablename(255));

* 13 rows in set (0.01 sec)

All indexes dropped ( except id and sitegroup ).

* 13 rows in set (19.25 sec)

Similiar query , but without parameters.

$qb = new midgardquerybuilder("midgard_topic");

$qb->add_constraint('up', '<>', 0);

$qb->add_constraint('metadata.created', '>', '2002-02-01 00:00:00');

$qb->add_constraint('metadata.published', '>', '2002-02-01 00:00:00');

I executed this query with and without metadata indexes. The difference between queries time was none.

Conclusion

Indexes for referenced properties ( on object level and "foreign keys" on database level ) are the keys for performance. And indexes used for queries when the same table is used seem to not increase performance at all.
However this can be different for queries which join the same table.