XF 2.3 Add an additional index to my entity aside from id?

[Newsman]

Hey there,

I was wondering: is there a way to add another index to my Entity aside from its primary key id? I read about adding an additional index via MySql and did so, but now I'm not sure how to proceed lol.

I appreciate any input!

Cheers!

 

[Jeremy P]

You can add keys using the schema manager:

$table->addKey(['column_one', 'column_two']);
$table->addUniqueKey(['column_one', 'column_two']);

 

[Newsman]

So I went ahead and did that, added a new key that should serve as an additional index - but there is no real difference in terms of performance unfortunately.

Just to clear up what I want to do: let's say I have the following data in my DB called 'apps'

[ id = 1, name = 'ABC', 'parent_app_id' = 0 ]
[ id = 2, name = 'DEF', 'parent_app_id' = 1 ]
.
.
.

I want to create a relation in the app entity that basically points at itself to check for the parent app using the parent_app_id column as an additional index similar to id. It's not that my current implementation does not work - it's just noticeably slower.

Do I have to change anything in my entity once I've made an additional key?

Cheers!

 

[Jeremy P]

Is it just a simple relation, or are there more conditions? You may need to look at the generated query. If it's a simple key lookup it should be pretty fast. If not, you may wish to run it with EXPLAIN FORMAT=JSON to profile it further.

 

[Newsman]

Is it just a simple relation, or are there more conditions? You may need to look at the generated query. If it's a simple key lookup it should be pretty fast. If not, you may wish to run it with EXPLAIN FORMAT=JSON to profile it further.

It is a simple relation from what I can tell

'RelatedApps' => [
    'entity' => 'Newsman:App',
    'type' => self::TO_MANY,
    'conditions' => [
        [ 'parent_app', '=', '$id' ]
    ],
    'primary' => true,
    'api' => true
],

 

[Newsman]

Oh wow, I seem to have solved this - the column was a LIST_COMMA column instead of an INT one

Blazing fast now, YES!

Thanks anyway @Jeremy P , I really appreciate the quick replies!

Cheers!

 

[Jeremy P]

Don't forget to remove the 'primary' => true definition if the relation is not a primary key lookup.