DeltaScripts

• Issues
• Login
• Register

• Issues:
• Issue List
• Changelogs
• Roadmaps
• Search

Issue #1 » Extra fields - Speed improvements

Project:	Issues | Roadmaps | ChangelogsPHP Classifieds	Status:	Implemented
Added On:	19/03/2009 18:21	Created By:	are
Last Updated:	Issue History 01/05/2009 22:28	Assigned To:	are
Resolved In:	Changelog 7.6	Affected Versions:	None
Private:	No	Notifications:	SubscribeNo Subscription

Description

Complex SQL joins are extremly slow in MySQL and this causes installs with many custom fields and a lot of ads to be very slow.

Steps to Reproduce

1. Create extra fields.
2. Fill with thousands of ads with extra field content.

Debug

Query History (10 Queries)

1. SELECT id, category, name, value FROM arctic_config
2. SELECT p.id, p.tag, pt.name AS type_name FROM arctic_permission p JOIN arctic_permission_type pt ON pt.id = p.type_id WHERE p.default_guest = 1
3. SELECT id, manager_id, title, description, private, date_added FROM arctic_project ORDER BY title
4. SELECT i.id, i.project_id, i.version_id, i.title, i.description, i.reproduce_steps, i.date_added, i.date_updated, i.private, p.id AS project_id, p.title AS project_title, s.id AS status_id, s.title AS status_title, s.prefix AS status_prefix, s.suffix AS status_suffix, s.open AS status_open, uadd.id AS user_added_id, uadd.username AS user_added_username, uass.id AS user_assigned_id, uass.username AS user_assigned_username, v.id AS target_version_id, v.number AS target_version_number, v.released as target_version_released FROM arctic_issue i JOIN arctic_project p ON p.id = i.project_id JOIN arctic_issue_status s ON s.id = i.status_id LEFT JOIN arctic_user uadd ON uadd.id = i.user_id_added LEFT JOIN arctic_user uass ON uass.id = i.user_id_assigned LEFT JOIN arctic_project_version v ON v.id = i.version_id WHERE i.id = 1 GROUP BY i.id
5. SELECT id, title, prefix, suffix, open, changelog, hide FROM arctic_issue_status
6. SELECT c.id, c.date_added, c.message, u.id AS user_id, u.username AS user_username FROM arctic_issue_comment c LEFT JOIN arctic_user u ON c.user_id = u.id WHERE c.issue_id = 1 ORDER BY c.date_added
7. SELECT h.date_performed, h.performer_id, h.language_phrase, h.history_values, u.username AS user_username FROM arctic_issue_history h LEFT JOIN arctic_user u ON u.id = h.performer_id WHERE h.issue_id = 1
8. SELECT c.id, c.type, c.title, c.default_value, c.options, c.required, v.id AS value_id, v.value FROM arctic_custom_field c LEFT JOIN arctic_custom_field_value v on v.field_id = c.id AND v.issue_id = 1 WHERE c.project_id IN (0, 1) AND v.value != "" AND v.value != "a:0:{}" ORDER BY c.display_order
9. SELECT id, issue_id, user_id, name, path, size, type, extension FROM arctic_issue_attachment WHERE issue_id = 1
10. SELECT v.id, v.project_id, v.number, v.description, v.date_released FROM arctic_project_version v JOIN arctic_issue_version iv ON iv.version_id = v.id WHERE iv.issue_id = 1

Statistics

• Script Execution Time: 0.031 seconds

| 10 Queries Back to DeltaScripts Homepage