Re: Indexes in Prelude.* tables

On Wed, Sep 10, 2008 at 10:22:13AM +0300, Alexander Afonyashin wrote:
> Hi all,
> 
> I've encountered a very annoying problem with deleting alerts from Mysql-based Prelude db. It seems it may takes forever to delete alerts for particular user's successful logins if there're more than 2 million records in Prelude_Alert tables (~20 million rows in Prelude_Address table etc.). By examining slow-queries log of Mysql I've found that there're a lot of DELETE requests that look like:
> 
> DELETE FROM Prelude_Alertident WHERE _message_ident IN (3458579, 3458582, 3458583, 3458584, 3458585, <more values to follow>);
> DELETE FROM Prelude_Node WHERE _message_ident IN (3458579, 3458582, 3458583, 3458584, 3458585, <more values to follow>);
> 
> There's no index on _message_ident field neither in these tables nor others. So it leads to full table scan afaik. Correct me if I'm wrong but adding index to table(s) like:
> 
> CREATE INDEX _message_ident_index ON _message_ident;
> 
> can greatly improve performance of DELETE operations in cases such mine.
> 

Hi,

I have checked here, it seems indeed that MySQL is not able to use a
part of a composite index:

mysql> explain select * from Prelude_Alertident WHERE _message_ident IN
(3458579, 3458582, 3458583, 3458584, 3458585);
.. | possible_keys | key | ..
      NULL         | NULL 

mysql> CREATE INDEX _pa_message_ident_index ON
Prelude_Alertident(_message_ident);
Query OK, 4539 rows affected (1.02 sec)
Records: 4539  Duplicates: 0  Warnings: 0

mysql> explain select * from Prelude_Alertident WHERE _message_ident IN
(3458579, 3458582, 3458583, 3458584, 3458585);

.. | possible_keys            | key                      | ..
      _pa_message_ident_index | _pa_message_ident_index

So it seems we have to create specific indexes on several columns ....

As far as I have checked, PostgreSQL does not have the same problem (it
can use a subset of a B-tree index), however the documentation says it's
very wise to use more than 3 columns in a index [1].

Time to re-index tables ? :)

Pierre

[1] http://postgresql.mirrors-r-us.net/docs/8.2/static/indexes-multicolumn.html
_______________________________________________
Prelude-devel site list
[email protected]
http://lists.prelude-ids.org/mailman/listinfo/prelude-devel