Automatic indexing

[gabordemooij]

Just an idea: optionally create indexes when using R::findLike() to put indexes on the criteria fields.

[marios88]

Indexes are pretty straitforward to create manually when you are done prototyping. Should you decide to follow through, i suggest you put this on config with default off

[gabordemooij]

Yes, we can use the feature-mechanism to opt-in for this.

[Lynesth]

I'm not sure about this to be honest. That means that a single findLike (one that was made for testing something for example) can create indexes on columns that could never be used in the code. This would slow every INSERT/UPDATE/DELETE queries for nothing.

Also this is something people need to learn and experiment with. Should they create a single column index to improve their query, a double column one, etc.

I'm much more in favour of a new function to create indexes (without having to do it in PHPMyAdmin or whatever) that would specify the bean type (the table) and an array with the type of indexes we want (UNIQUE, etc) on which columns.

Here's how I'd see it:

R::createIndexes(
    'book',
    [
        'isbn' => 'unique',
        'title' => 'index',
        'author_id' => 'index',
        'title/author_id' => 'index'
    ]
);

// Some flexibility in the array notation would allow what's written above to be the same as:
R::createIndexes(
    'book',
    [
        'isbn' => 'unique',
        'title',
        'author',
        'title/author'
    ]
);

It would of course only work in fluid mode. A simple R::getIndexes( 'book' ) could also be created that would return a list of indexes for a specific bean/table.

[flip111]

That means that a single findLike (one that was made for testing something for example) can create indexes on columns that could never be used in the code

I think it's already good practice to rebuild or clean the database before going into production. If you accidentally create unnecessary tables, indexes or other artifacts they must be cleaned. I think this feature proposition is in the spirit of redbeanPHP.

[gabordemooij]

@Lynesth well I guess it has it's use. I am working on an import script right now and I know that the fields used in find should also be the ones to be indexed, so it would save me some time to have it in one go. But these things are always tricky and they can bite you, so being extra cautious here is good.

Some ideas:

1

R::findLike( $type, $array, $sql, $bindings, $addIndex )
with alias: R::findLikeIndex( $type, $array, $sql, $bindings )

2

R::useAutoIndex( TRUE )
R::findLike( $type, $array, $sql, $bindings )

[Lynesth]

Well, I suppose that as long as it doesn't add too much logic and that it isn't activated by default, I don't mind. I know I will most likely not use it and that I'd prefer a function dedicated to create/modify indexes.

[gabordemooij]

There is already a function to add indexes:

$writer->addIndex( ... )

https://redbeanphp.com/api/classes/RedBeanPHP.QueryWriter.MySQL.html#method_addIndex

[Lynesth]

Yes and there's the addUniqueIndex as well. But I was thinking of having only one function that could add as many indexes on a table (bean) as needed in one call and that could also manage composite indexes.

[gabordemooij]

Okay, interesting idea. But how would that work in the workflow of RedBeanPHP? It has been designed to create the database schema on-the-fly, but creating composite indexes seems to be a one-time only operation. Of course we have some edge cases already, for instance $book->xownPageList[] creates a cascading foreign key book.id->page.book_id the first time. Can you elaborate a bit?

[alystair]

This concept could work if there was a redbean metadata table that collected usage stats to intelligently advise re: indexes - would be feature creep and might cause significant edge cases if it actually executes the recommendations... probably not worth the effort!

[gabordemooij]

Agreed, not worth the effort