Support

December 3, 2015 at 5:06 pm

Hi guys,

My name is Greg Borbonus, and I’ve been asked to address an issue with you guys by one of my clients.

Recently, I was asked to investigate why their system was so slow, we found out that the host was limiting their mysql transactions due to the fact that their website was using a lot of mysql resources during the queries.

So, I dug into the queries being generated.

There was one that was taking WAY too long to reply, like 20 seconds when the server had no load:

SELECT Datetime.DTT_ID AS ‘Datetime.DTT_ID’, Datetime.EVT_ID AS ‘Datetime.EVT_ID’, Datetime.DTT_name AS ‘Datetime.DTT_name’, Datetime.DTT_description AS ‘Datetime.DTT_description’, Datetime.DTT_EVT_start AS ‘Datetime.DTT_EVT_start’, Datetime.DTT_EVT_end AS ‘Datetime.DTT_EVT_end’, Datetime.DTT_reg_limit AS ‘Datetime.DTT_reg_limit’, Datetime.DTT_sold AS ‘Datetime.DTT_sold’, Datetime.DTT_is_primary AS ‘Datetime.DTT_is_primary’, Datetime.DTT_order AS ‘Datetime.DTT_order’, Datetime.DTT_parent AS ‘Datetime.DTT_parent’, Datetime.DTT_deleted AS ‘Datetime.DTT_deleted’, Event_CPT.ID AS ‘Event_CPT.ID’, Event_CPT.post_title AS ‘Event_CPT.post_title’, Event_CPT.post_content AS ‘Event_CPT.post_content’, Event_CPT.post_name AS ‘Event_CPT.post_name’, Event_CPT.post_date AS ‘Event_CPT.post_date’, Event_CPT.post_excerpt AS ‘Event_CPT.post_excerpt’, Event_CPT.post_modified AS ‘Event_CPT.post_modified’, Event_CPT.post_author AS ‘Event_CPT.post_author’, Event_CPT.post_parent AS ‘Event_CPT.post_parent’, Event_CPT.menu_order AS ‘Event_CPT.menu_order’, Event_CPT.post_status AS ‘Event_CPT.post_status’, Event_CPT.comment_status AS ‘Event_CPT.comment_status’, Event_CPT.ping_status AS ‘Event_CPT.ping_status’, Event_Meta.EVT_display_desc AS ‘Event_Meta.EVT_display_desc’, Event_Meta.EVT_display_ticket_selector AS ‘Event_Meta.EVT_display_ticket_selector’, Event_Meta.EVT_visible_on AS ‘Event_Meta.EVT_visible_on’, Event_Meta.EVT_additional_limit AS ‘Event_Meta.EVT_additional_limit’, Event_Meta.EVT_default_registration_status AS ‘Event_Meta.EVT_default_registration_status’, Event_Meta.EVT_member_only AS ‘Event_Meta.EVT_member_only’, Event_Meta.EVT_phone AS ‘Event_Meta.EVT_phone’, Event_Meta.EVT_allow_overflow AS ‘Event_Meta.EVT_allow_overflow’, Event_Meta.EVT_timezone_string AS ‘Event_Meta.EVT_timezone_string’, Event_Meta.EVT_external_URL AS ‘Event_Meta.EVT_external_URL’, Event_Meta.EVT_donations AS ‘Event_Meta.EVT_donations’, Event_CPT.ID AS ‘Event_CPT.ID’, Event_Meta.EVTM_ID AS ‘Event_Meta.EVTM_ID’ FROM wp_esp_datetime AS Datetime LEFT JOIN wp_posts AS Event_CPT ON Event_CPT.ID=Datetime.EVT_ID LEFT JOIN wp_esp_event_meta AS Event_Meta ON Event_CPT.ID = Event_Meta.EVT_ID WHERE Event_CPT.ID = 4200 AND Datetime.DTT_EVT_end >= ‘2015-12-02 23:06:24’ ORDER BY Datetime.DTT_order ASC

All of the queries in that format (the where conditions changed) were all acting the same.

From my analysis of the query and the table, this is not optimized at all, matter of fact, the more you add, the slower this thing gets. It’s not using any keys that would make it quicker and use less memory.

A simple describe shows it as having almost no cardinality, and the indexes that are available are not being used well.

Do you guys have any experience with similar issues?

IF so, what do you do to overcome them?