Managing indexes with RedBeanPHP

[Marko Tukiainen]

Hi,

Would it be possible to add a feature to RedBeanPHP to manage additional indexes in tables? I'm aware of calling R::$writer->addIndex() but I'm looking for something more elegant. Here's how I think it could/should work:

• Indexes would be only be created in fluid mode. 
  
• The index definitions could be set in the Model
• Index management should ideally only be done when necessary, not with every write. 
• Perhaps at the same time when RedBean makes changes to a table?
• Of course when you only add/remove/change an index definition there should either be some other way to trigger index creation or some way to track changes to the index definitions. 

I suppose an alternative way to do this would be to create some separate management script, like 'update-indexes.php' that would call R::$writer->addIndex() for all the indexes that are needed in the database and remove the ones that are no longer in use. But having RedBeanPHP take care of this transparently would be ideal. ;)

Marko

[Zewa One]

Not a bad idea to have RB create indexes in general, on the other hand indexes are typically something that is placed when your database structure is already set. So not sure why you would wan't to add it in fluent mode. Also once the structure is complete and fluid mode is turned off, you could easily add your Indexes in the management studio of choice.

[Marko Tukiainen]

Obviously you want to add indexes for increased performance, regardless of the mode. Indexes have a different kind of effect on performance than frozen mode. If an app does one write per one million reads (with a search on a column that's not indexed) it won't matter much if RedBeanPHP is running in frozen or fluid mode as far as total performance is concerned.  

A couple of use cases:

- Apps that never get "frozen". Some might call it heresy to use fluid mode in production, but it has its uses. 

- Apps that create multiple instances of the same database structure - for example, one per customer. Sure, you could do this with a script, but RedBeanPHP creating the structure automatically is just so much more convenient. 

Marko

[Zewa One]

Oh I do absolutely get the point regarding Index usage and benefits also during dev-mode, that wasn't the point, I just wasn't sure why you'd like to have indexes only created during fluid mode.

I do not necessarily add indexes on each potential search field but rather let the use-case decide where a single or combined index is needed. Thus this may also happen when the overall

DB structure is already set.

Anyway generally I like the idea to have some "intelligent/elegant" way to handle indexes through code. 

Regarding your use cases later case makes sense in cases of a decentralized applications without knowing more details about your project, but why would you not freeze the app when in prod? I'm just curios what necessity makes you skip the overall

performance increase of a frozen db structure.

Greetz

Zewa

[gabor]

Interesting idea.
I have written functionality like this before but it did not work.
The core problem was the difference between frozen and fluid mode, a fluid database is often way too different to infer what indexes are
necessary, the data is simply not available.

And, yes there is a government organisation running RedBeanPHP in fluid mode.
They told me they simply don't care about performance because they have enough 'iron' to power the beast.
I don't think we should write features for these people, they are often in a very different business. It's better to focus on the
average developer who can't afford to run in fluid mode.

cheers
Gabor

[Zewa One]

Well, I don't care, is actually neither a functional nor non-functional argument to keep it on fluid. It's like going to buy milk with your Jumbo Jet, just because you can afford to do so :-)

Btw. I'm not sure if Marko ment to let RB decide which indexes are necessary, but rather easy the management of them. Please correct me if I'm wrong but my understanding of this

was to provide easy methods to e.g. create combined indexes, or define sort-order or things like that NOT to let RB decide -- oh this is a varchar field, lets make an fulltext index -- kinda thing :-)

Again maybe I just misunderstood it.

I'd personally never ever let any automated script decide what Indexes I should use.

Lastly I'd argue not to exclude anybody, but maybe just point into another direction. So instead of adding it to the core this maybe could be provided as a kinda Plugin or even

an overridden Writer/Driver implementation.

[Marko Tukiainen]

I wasn't suggesting that RedBeanPHP should add indexes automatically - that would most likely result in a disaster. 

What I would like is to be able to define the indexes in code - for example in the models - and have RedBeanPHP apply the changes to the database(s) on the fly. 

Don't take this the wrong way Gabor, but are you sure you know what the absolutely best thing about RedBeanPHP is? At least for me it's the fluid mode, hands down. And not just for the development but also for the production. We've used RedBeanPHP in at least six applications, and so far all of them are still running in fluid mode. Why? Because they don't get that much traffic (yet) and some of them are still evolving. If I took fluid mode off in production I would have to ensure that all the databases have the correct, full schema. (Well, this is what we do with our more "traditionally" developed applications, and frankly, I think it's a pain in the ass.) Some of the apps may end up getting more traffic later, and I expect to switch some of them to frozen mode at some point. 

And as far as the average coder is concerned, I think many of them will have low traffic applications and sites, and would be perfectly fine with fluid mode. It's just that indexing would make some use cases faster for those few users, because single queries would be that much faster. I realize that no one is keeping me from adding the indexes manually, but if the application has multiple databases - and most of our apps do - the operation would have to be scripted somehow, and it would have to ensure that the tables / fields exist in all the databases in the first place. 

Marko

[gabor]

Hm,

I don't think you really want that.
There are several problems with fluid mode on a production server:

1. You can't ensure data integrity (all databases kill transactions after a schema modification) - so you might be vulnerable to race conditions
2. You get all the trouble that NoSQL databases have like column-injection attack vectors (http://mysqltalk.wordpress.com/2011/12/19/you-thought-sql-injection-was-bad-schema-injection-coming-to-a-nosql-site-near-you/)
3. Also some columns can get locked, for instance if you first insert a date one will create a date column, and this column won't change anymore because RB can't predict how to cast the values


Also, if you use the SQLite driver, it will eat your server for breakfast because SQLite does not support certain ALTER-commands, so guess how RedBeanPHP modifies these tables?

cheers,
Gabor

[Zewa One]

I guess there are ton's of ways to develop a project. My typical workflow for LOB apps, be it web/desktop involve a dev/quality/prod environment, which prohibits changes on prod but only pushes from approved quality environments.

Thus it makes no sense to leave fluid mode on. A simple script can pull the tested and verified version from your continuous integration server/svn/ or whatever deployment method you use and freeze the db.

But again everybody has a preferred way of development and every business is different.

Nevertheless the idea of having index management capabilities inside RB sounds still interesting.

Marko could you show a simple pseudo-coded model of features you'd like to use? Really just some hint's or method-stubs.

Coming from the .NET world a typical approach for me would to flavor models with arguments, yeah I know PHP doesn't support it naturally but it can via Reflection and Comments.

The problem I'd see here is that this is against the "configuration-less" idea of RedBean.

Greetz

Zewa

[Marko Tukiainen]

I realize fluid mode is not optimal and there are certain security implications. Filtering imported variables helps a lot, though. So far I haven't had problems, and if I do, there's always the freezing option. 

But back to my original idea: I think it would be nice to define the indexes in code instead of in the database. Having one place to set all the rules and factors that affect the application would be great. 

As far as the practical implementation goes, I haven't given it much thought. Maybe just some simple mapping of property names and the types of indexes they would need. 

Marko

[Zewa One]

I honestly miss the times when "Database Administrator" was a real and regular job description. Nowadays proper DB design has become so unimportant due to lack of understanding or highly overpowered hardware backends.

Placing indexes is a typical example where quick'n'dirty solutions lead to long-time problems which are hard to track down. For a current customer of mine I use to work with a old-school DB Admin on MSSQL Server

where planing Indexes involves whole routines like going through Query Execution Plans, planing how your data is structured and thus deciding what the best type of index will be.

There's more than simply setting a Unique/Non-unique index on a column. Depending on your data / rdbms combined index performance e.g. vary with respect to their column order, sorting and potential conflicts with other existing indexes.

So in my opinion a simple property mapping is definitely a nice approach for developers who simply don't care about proper DB designs. But getting your hands dirty on proper design requires better tools.

The more I think about this topic the more I start to dislike the feature because in the end it potentially leads to unconscious decisions. Also since RDBMs use to treat indexing different in detail I see a lot of

work on the Writer classes to take care of specialized treatments.

Greetz

Zewa

[Marko Tukiainen]

This discussion is beginning to sound like "when I was young we had to ski to school 30 kilometers a day all winter and summer long"... :)

You could just as well say that RedBeanPHP is for people who don't care about proper DB designs, and that using it very easily leads to unconscious decisions that result in code that generates 100x more queries that would be needed if you wrote every query by hand. Still its benefits far outweigh the problems. It's not a tool that is supposed to handle and solve complex database administration problems - instead I see it as a handy toolkit for the solo programmer-dba who has to persist some fairly simple data somewhere. If it could additionally provide some optional features for optimization (like it currently does with the preload method), even if they're not perfect, why not? 

Still, if you approach the problem from a more traditional sense that programming and database administration should be kept separate and preferably done by different people, defining indexes has no place in code - but that's just as true for creating the database tables. Or if you view the fluid mode only as a prototyping tool, why not use it for prototyping indexing as well? I may be wrong but in the real world of PHP development the percentage of developers who have a full-time database administrator in their team - or who just happen to be an expert in the field themselves - is pretty low.  

Most of the data stored and read by RedBeanPHP is pretty simple and straightforward, and I would bet that most of the indexing scenarios would be as well. Of course as a programmer you have to start by looking at the worst case scenario and start working from there, but I think that if you dismiss the idea based on the premise that "it could be used wrong" you're throwing the baby out with the bath water. 

Marko

[Zewa One]

Am Samstag, 19. Oktober 2013 16:10:34 UTC+2 schrieb Marko Tukiainen:

This discussion is beginning to sound like "when I was young we had to ski to school 30 kilometers a day all winter and summer long"... :)

Interesting childhood :)

The nice thing about Open Source Community driven stuff is that you have the chance to talk about things like this. So first of all I don't want to criticize your or anybodies way how they develop software nor am I going to decide what happens. I'm just a random RB Fan hopefully just like you :) We all got our opinions the interesting part is to find a common sense helping everybody.  

 

You could just as well say that RedBeanPHP is for people who don't care about proper DB designs, and that using it very easily leads to unconscious decisions that result in code that generates 100x more queries that would be needed if you wrote every query by hand. Still its benefits far outweigh the problems.

True words, thou the difference is that you have all the tools to make it right. If you care about performant queries, use manual SQL instead of ORM generated Beans.

 

It's not a tool that is supposed to handle and solve complex database administration problems - instead I see it as a handy toolkit for the solo programmer-dba who has to persist some fairly simple data somewhere

OK I understand that, but my view on it is completely different since I'm not using RedBean to create my tables in a prototypical way but to easy work with

a already set database structure. Thus I can't agree with the statement that you're not supposed to solve complex tasks. Again if ORM doesn't suffice do it the SQL way. Thats actually the one big difference to all the other ORMs in my mind. Redbean seems not trying to convince you into an abstracted query language.

 

If it could additionally provide some optional features for optimization (like it currently does with the preload method), even if they're not perfect, why not? 

As said I feel kinda biased. On the one hand I agree that it makes easy stuff faster to be done, on the other hand I don't see that big benefit to include that into the core of RB if you just do the half thing. As you already mentioned for simple index creation there is an addIndex feature which helps already.

Still, if you approach the problem from a more traditional sense that programming and database administration should be kept separate and preferably done by different people, defining indexes has no place in code - but that's just as true for creating the database tables. 

Or if you view the fluid mode only as a prototyping tool, why not use it for prototyping indexing as well? I may be wrong but in the real world of PHP development the percentage of developers who have a full-time database administrator in their team - or who just happen to be an expert in the field themselves - is pretty low.  

Which in my opinion is exactly the reason because of tools like Access etc. where you drag&drop stuff but don't learn the things beneath the surface.

The problem is as soon as it gets interesting you have to skip those tools and dive into real work. That suddenly leads, at least for me, to some disappointment about the tool because it offers you just not what is needed.

So my personal opinion is don't offer half-baked solutions and since those would be quite hard to implement I ask myself if it makes sense to do it at all. As you mentioned a simple script/sql client could do the trick also.

 

Most of the data stored and read by RedBeanPHP is pretty simple and straightforward, and I would bet that most of the indexing scenarios would be as well.

Of course as a programmer you have to start by looking at the worst case scenario and start working from there, but I think that if you dismiss the idea based on the premise that "it could be used wrong" you're throwing the baby out with the bath water.  

Marko

Greetz

Zewa 

[Dave Page]

Just my two cents - I would not put this in core but perhaps as a plug in.  I tend to index where obvious and generally after everything is frozen. 
I use redbeanphp for most of my projects along with the Slim framework but recently collaborated with others using the Laravel framework.
Its Eloquent ORM has some simple indexing capabilities.

Example syntax:

Adding a primary key:    $table->primary('id'); 
Adding composite keys:   $table->primary(array('first', 'last'));   
Adding a unique index:   $table->unique('email');   
Adding a basic index:    $table->index('state');

It also has a method for adding foreign key constraints:

$table->foreign('user_id')->references('id')->on('users');

I like it but I'm still a traditionalist (read old ;) ) and like to wear my db admin hat after I've got the db pretty stable then add the indices as needed.

Dave

--
You received this message because you are subscribed to the Google Groups "redbeanphp" group.
To unsubscribe from this group and stop receiving emails from it, send an email to redbeanorm+...@googlegroups.com.
To post to this group, send email to redbe...@googlegroups.com.
Visit this group at http://groups.google.com/group/redbeanorm.
For more options, visit https://groups.google.com/groups/opt_out.