View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0003780 | Composr | core_database_drivers | public | 2019-02-22 14:58 | 2019-11-24 02:23 |
| Reporter | Chris Graham | Assigned To | |||
| Severity | feature | ||||
| Status | non-assigned | Resolution | open | ||
| Product Version | |||||
| Fixed in Version | |||||
| Summary | 0003780: Support upsert on non-MySQL backends (Make use of MySQL REPLACE INTO syntax) | ||||
| Description | put_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. | ||||
| Tags | Type: Performance | ||||
| Attach Tags | |||||
| Time estimation (hours) | 3 | ||||
| Sponsorship open | |||||
|
|
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. |
|
|
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. |
|
|
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. |
| 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 |
