Thread

Instead of using the ‘standard’ custom fields in exp_weblog_data, I would want to use a custom field in exp_weblog_titles. The reason for this is performance.

I have multiple weblogs (with different custom field groups) that all should have one and the same additional custom field on which I can filter.

If I add a field to the table exp_weblog_data and make it indexed (or use the existing pentry_id field), then I can filter all entries very fast on that field.

So reading is easy but how do I write to this field?

I will never use the CP but only the SAEF to write new entries. Simply adding the field in the entry form does not work.

I first looked into mod.weblog_standalone.php but do not see the DB insert happening there.
I’m now looking into cp.publish.php where I see a DB insert. I guess I’ll have to hack this one but I’m not sure this is the right place or how the data moves from the SAEF form to here.

Anyone knows what would need to be done where?

Thanks

I’m not quite following what you’re saying here, fbossuyt - but I would say for sure that this would not be an officially supported change.  I would strongly recommend sticking with the standard custom field implementation.

What Lisa says, and don’t worry about performance too much. JOINing two tables really has little impact on overall performance, if everything else works.

I agree that the JOIN of the 2 tables is not a problem, but I’m worried about the WHERE clause when the standard custom fields are not indexed.

I don’t know much about databases but from what I read, I thought there could be a significant difference in performance.

Suppose that I have 2 mio entries: 1mio entries with weblog_ID=2 and 1mio with weblog_ID=2, and for each entry a value from 1 to 1000 in the custom field that I always need to filter on.

I think MySQL would use the weblog_id index to split the number of records in 2, and then would need to scan the custom field for the 1mio records

If the custom field id were indexed, then MySQL could use that one to split the number of records in 1000, and only would need to scan 2000 records for the correct weblog_id

If both fields are in 1 table, and an index is created on the combination of the 2 fields, then I guess it could address all entries directly by only using the index table.

Is this a correct interpretation? and from what number of entries would one notice a difference in performance?

I’ll never get that number of entries but just figured, if all entries for all sites and all weblogs are stored in the same table, I better make it as efficient as possible, so I ‘ll never need to optimize things later.

I repeat, don’t worry about it unless server load forces your hand, so to speak. And even then you have a number of options. Hacking EE would be last on my agenda (if at all).

Thanks for the answers. I’ll stop worrying and leave everything as is.

In the meanwhile, found that it can be done and with an extension and without a hack.

I guess just adding a field and an index to a table in MySQL would not impact EE at all and would not be considered as a hack.