MySQL Query performance reference, with many of the same table being joined

I am writing a PHP script that creates a SQL query. This script and database is for Joomla CMS and in particular it queries the SOBIPro component tables (to use the data entered there in that component). However, due to SOBI Pro tables being processed, each field instance is its own row in the table, this means including a separate table instance for each field that I want to undo. It doesn't seem to be very effective, and it actually is in this search mode.

The SQL query looks like this (it happens after my PHP code):

    SELECT DISTINCT AS entryid, o.parent AS parentID, name.baseData AS title,business.baseData AS business_data,
    contact_fn.baseData AS contact_fn_data ,contact_ln.baseData AS contact_ln_data ,position.baseData AS position_data,
    civic1.baseData AS civic1_data ,civic2.baseData AS civic2_data ,mailing.baseData AS mailing_data,
    community.baseData AS community_data ,municip.baseData AS municip_data ,county.baseData AS county_data,
    province.baseData AS province_data ,country.baseData AS country_data ,postal.baseData AS descr_data,
    phone.baseData AS phone_data ,tollfree.baseData AS tollfree_data ,fax.baseData AS fax_data,
    email.baseData AS email_data ,web.baseData AS web_data ,empTotal.baseData AS empTotal_data
    FROM jos_sobipro_object AS o
    INNER JOIN jos_sobipro_field_data AS name ON name.sid =
    INNER JOIN jos_sobipro_relations AS r ON =
    LEFT JOIN jos_sobipro_field_data AS business ON business.sid = AND business.fid = 36
    LEFT JOIN jos_sobipro_field_data AS contact_fn ON contact_fn.sid = AND contact_fn.fid = 74
    LEFT JOIN jos_sobipro_field_data AS contact_ln ON contact_ln.sid = AND contact_ln.fid = 75
    LEFT JOIN jos_sobipro_field_data AS position ON position.sid = AND position.fid = 76
    LEFT JOIN jos_sobipro_field_data AS civic1 ON civic1.sid = AND civic1.fid = 77
    LEFT JOIN jos_sobipro_field_data AS civic2 ON civic2.sid = AND civic2.fid = 78
    LEFT JOIN jos_sobipro_field_data AS mailing ON mailing.sid = AND mailing.fid = 79
    LEFT JOIN jos_sobipro_field_data AS community ON community.sid = AND community.fid = 80
    LEFT JOIN jos_sobipro_field_data AS municip ON municip.sid = AND municip.fid = 81
    LEFT JOIN jos_sobipro_field_data AS county ON county.sid = AND county.fid = 82
    LEFT JOIN jos_sobipro_field_data AS province ON province.sid = AND province.fid = 83
    LEFT JOIN jos_sobipro_field_data AS country ON country.sid = AND country.fid = 84
    LEFT JOIN jos_sobipro_field_data AS postal ON postal.sid = AND postal.fid = 85
    LEFT JOIN jos_sobipro_field_data AS phone ON phone.sid = AND phone.fid = 86
    LEFT JOIN jos_sobipro_field_data AS tollfree ON tollfree.sid = AND tollfree.fid = 87
    LEFT JOIN jos_sobipro_field_data AS fax ON fax.sid = AND fax.fid = 88
    LEFT JOIN jos_sobipro_field_data AS email ON email.sid = AND email.fid = 89
    LEFT JOIN jos_sobipro_field_data AS web ON web.sid = AND web.fid = 90
    LEFT JOIN jos_sobipro_field_data AS empTotal ON empTotal.sid = AND empTotal.fid = 106
    WHERE o.approved = 1 AND o.oType = 'entry' AND name.fid = 36 AND name.baseData <> ''
    AND name.section = 54 AND IN (415,418,425,431,458) AND (municip.baseData = "Municipality Name")
    ORDER BY name.baseData ASC


It seems to work pretty fast as long as the search for municip.baseData is not involved, in which case it falls through even 15 entries in the directory. There must be a better way to get this SQL generated while still returning all the required fields. This request is called via AJAX and will end up with over 2000 entries in the directory.

EDIT: Here's the EXPLAIN output as requested:

    id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
    1   SIMPLE  name    ref     PRIMARY     PRIMARY     8   const,const     15  Using where; Using temporary; Using filesort
    1   SIMPLE  municip     ref     PRIMARY     PRIMARY     4   const   9   Using where
    1   SIMPLE  o   eq_ref  PRIMARY,oType   PRIMARY     4   [[dbname]].municip.sid  1   Using where
    1   SIMPLE  county  ref     PRIMARY     PRIMARY     4   const   10  
    1   SIMPLE  province    ref     PRIMARY     PRIMARY     4   const   10  
    1   SIMPLE  country     ref     PRIMARY     PRIMARY     4   const   8   
    1   SIMPLE  postal  ref     PRIMARY     PRIMARY     4   const   9   
    1   SIMPLE  business    ref     PRIMARY     PRIMARY     4   const   15  
    1   SIMPLE  contact_fn  ref     PRIMARY     PRIMARY     4   const   9   
    1   SIMPLE  contact_ln  ref     PRIMARY     PRIMARY     4   const   9   
    1   SIMPLE  position    ref     PRIMARY     PRIMARY     4   const   9   
    1   SIMPLE  civic1  ref     PRIMARY     PRIMARY     4   const   10  
    1   SIMPLE  civic2  ref     PRIMARY     PRIMARY     4   const   9   
    1   SIMPLE  phone   ref     PRIMARY     PRIMARY     4   const   10  
    1   SIMPLE  tollfree    ref     PRIMARY     PRIMARY     4   const   9   
    1   SIMPLE  fax     ref     PRIMARY     PRIMARY     4   const   10  
    1   SIMPLE  email   ref     PRIMARY     PRIMARY     4   const   9   
    1   SIMPLE  mailing     ref     PRIMARY     PRIMARY     4   const   11  
    1   SIMPLE  community   ref     PRIMARY     PRIMARY     4   const   9   
    1   SIMPLE  web     ref     PRIMARY     PRIMARY     4   const   10  
    1   SIMPLE  empTotal    ref     PRIMARY     PRIMARY     4   const   10  
    1   SIMPLE  r   ref     PRIMARY     PRIMARY     4   [[dbname]].name.sid     3   Using where; Using index; Distinct



source to share

1 answer

Many times, when you have a JOIN / JOIN / JOIN / etc overly extended as you have, the SQL engine will freeze on its own trying to find small result sets and reconfigure the binding in a less efficient way. Your request LOOKS well.

Your PRIMARY table (FROM jos_sobipro_object AS o) is indeed the key management for the query. Try adding the special keyword "STRAIGHT_JOIN" with MySQL ..


STRAIGHT_JOIN tells the optimizer to simply execute the query in the order I specified. Then it will run faster. VALUE The first table is the primary one for querying data.

However, and not seeing the index information exactly, I would SPECIALLY have an index on jos_sobipro_field_data to get the "lookup" data (SID, FID).

I had to do a similar approach with data from 14+ million records in the main table and join 22+ lookup tables. MySQL will hang in 30 hours. By adding STRAIGHT_JOIN, the request completed in about 3 hours (as expected it did).



All Articles