View Issue Details

IDProjectCategoryView StatusLast Update
0003780Composrcore_database_driverspublic2019-11-24 02:23
ReporterChris GrahamAssigned To 
Severityfeature 
Status non-assignedResolutionopen 
Product Version 
Fixed in Version 
Summary0003780: Support upsert on non-MySQL backends (Make use of MySQL REPLACE INTO syntax)
Descriptionput_into_cache and set_value are both highly trafficed functions that do a query_delete (DELETE FROM) followed by a query_insert (INSERT INTO). These cases could be single REPLACE INTO calls in MySQL.

The ideal solution would be to extend the insert_into method to change $fail_ok to take constants QUERY_FAIL_HARD, QUERY_FAIL_SILENT, and QUERY_REPLACE_INTO.
Then each database driver could do it's own implementation. Almost every database backend has some kind of syntax for it https://en.wikipedia.org/wiki/Merge_(SQL)

However, a quick and dirty detection of whether MySQL is running, and coding in an alternative query, would be fine.
TagsType: Performance
Attach Tags
Time estimation (hours)3
Sponsorship open

Activities

Chris Graham

2019-11-20 20:10

administrator   ~0006164

Priority of this is raised if using InnoDB, as additional write queries has non-trivial overhead in terms of establishing logs and updating the ACID log.

These functions in v9 are worth looking at (initially implementing this as a sponsorship for a customer)...

get_num_users_site
render_field_value url.php
put_into_cache
do_comcode_attachments
_do_tags_comcode
ocf_ping_topic_read
edit_ping_script
find_theme_image
check_url_exists
_update_read_status
member_tracking_update
set_tutorial_link

Note that most DBs that implement a merge feature do not do it as simply as MySQL. For us to support those we'd either need to pass in the key fields with each REPLACE query, or we'd need to read them from the DB - complexity that I think is not worth it.

Therefore I think a better implementation is to make a new query_insert_or_replace database method, and if this returns false, do the current method of delete then insert with error ignoring. Messy, but we're only targeting changes to a few critical highly trafficked cases here.

Chris Graham

2019-11-20 20:38

administrator   ~0006165

Actually, I've implemented query_insert_or_replace as a similar function signature to query_update and query_delete. i.e. separated the key params+values from the rest of the params+values. This works nicely, as no extra parameters are needed for the DB layer to fallback to the old query_delete/query_insert way of doing things.

Chris Graham

2019-11-24 02:23

administrator   ~0006168

This is now implemented for MySQL, and can be implemented by any DB driver that wants to. I'm leaving this open for if we want to implement on other DB backends.
The syntax is lot cleaner on MySQL.

Issue History

Date Modified Username Field Change
2019-02-22 14:58 Chris Graham New Issue
2019-02-22 14:58 Chris Graham Tag Attached: Type: Performance
2019-11-20 20:10 Chris Graham Note Added: 0006164
2019-11-20 20:38 Chris Graham Note Added: 0006165
2019-11-24 02:22 Chris Graham Summary Make use of MySQL REPLACE INTO syntax => Support upsert on non-MySQL backends (Make use of MySQL REPLACE INTO syntax)
2019-11-24 02:23 Chris Graham Time estimation (hours) 1 => 3
2019-11-24 02:23 Chris Graham Note Added: 0006168