82305

MySQL Query performance help, many of the same table being joined

Question:

I am writing a PHP script that creates an SQL query. This script and database is for the Joomla CMS, and specifically it's querying the SOBIPro component's tables (to use the data entered there in this component). However, due to the way that the SOBI Pro tables are handled, with each instance of a field being its own row in a table, this means including a separate instance of the table for every field I want to pull back. This doesn't seem very efficient, and in fact in this one search it times out.

The SQL query is as follows (this is after being generated by my PHP code):

SELECT DISTINCT o.id 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 = o.id INNER JOIN jos_sobipro_relations AS r ON o.id = r.id LEFT JOIN jos_sobipro_field_data AS business ON business.sid = o.id AND business.fid = 36 LEFT JOIN jos_sobipro_field_data AS contact_fn ON contact_fn.sid = o.id AND contact_fn.fid = 74 LEFT JOIN jos_sobipro_field_data AS contact_ln ON contact_ln.sid = o.id AND contact_ln.fid = 75 LEFT JOIN jos_sobipro_field_data AS position ON position.sid = o.id AND position.fid = 76 LEFT JOIN jos_sobipro_field_data AS civic1 ON civic1.sid = o.id AND civic1.fid = 77 LEFT JOIN jos_sobipro_field_data AS civic2 ON civic2.sid = o.id AND civic2.fid = 78 LEFT JOIN jos_sobipro_field_data AS mailing ON mailing.sid = o.id AND mailing.fid = 79 LEFT JOIN jos_sobipro_field_data AS community ON community.sid = o.id AND community.fid = 80 LEFT JOIN jos_sobipro_field_data AS municip ON municip.sid = o.id AND municip.fid = 81 LEFT JOIN jos_sobipro_field_data AS county ON county.sid = o.id AND county.fid = 82 LEFT JOIN jos_sobipro_field_data AS province ON province.sid = o.id AND province.fid = 83 LEFT JOIN jos_sobipro_field_data AS country ON country.sid = o.id AND country.fid = 84 LEFT JOIN jos_sobipro_field_data AS postal ON postal.sid = o.id AND postal.fid = 85 LEFT JOIN jos_sobipro_field_data AS phone ON phone.sid = o.id AND phone.fid = 86 LEFT JOIN jos_sobipro_field_data AS tollfree ON tollfree.sid = o.id AND tollfree.fid = 87 LEFT JOIN jos_sobipro_field_data AS fax ON fax.sid = o.id AND fax.fid = 88 LEFT JOIN jos_sobipro_field_data AS email ON email.sid = o.id AND email.fid = 89 LEFT JOIN jos_sobipro_field_data AS web ON web.sid = o.id AND web.fid = 90 LEFT JOIN jos_sobipro_field_data AS empTotal ON empTotal.sid = o.id AND empTotal.fid = 106 WHERE o.approved = 1 AND o.oType = 'entry' AND name.fid = 36 AND name.baseData <> '' AND name.section = 54 AND r.pid IN (415,418,425,431,458) AND (municip.baseData = "Municipality Name") ORDER BY name.baseData ASC

It seems to work decently fast as long as the municip.baseData search isn't involved, in which case it flops even at 15 entries in the directory. There has to be a better way to get this SQL code designed, while still bringing back all of the fields needed. This query is called via AJAX, and eventually there will be 2000+ entries in the directory.

EDIT: Here is 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

Answer1:

Many times, when you have an overly extended JOIN/JOIN/JOIN/etc as you have, the SQL engine will get hung on itself trying to find small result sets and backfil the linking in a less efficient manner. Your query LOOKS good.

Your PRIMARY table (FROM jos_sobipro_object AS o) is really the KEY driving element to the query. Try adding the "STRAIGHT_JOIN" special keyword with MySQL..

SELECT STRAIGHT_JOIN DISTINCT ... rest of query ...

STRAIGHT_JOIN tells the optimizer to just DO the query in the order I've listed here. Then it will work faster KNOWING the first table is the primary for querying the data.

That said, and not exactly seeing index info, I would SPECIFICALLY have an index on jos_sobipro_field_data to GET the "lookup" data by (SID, FID).

I've had to do similar approach with govt data of 14+ million records in main table and joining to 22+ lookup tables. MySQL would hang after 30+ hours. By adding STRAIGHT_JOIN, the query finished in about 3 hours (as expected by what it was doing).

Recommend

  • c++ boost program options always giving default value
  • boost program_option case insensitive parsing
  • Using PrivateObject.Invoke to call a static conversion function do not compile in C#
  • PHP gettext: not all strings becomes translated
  • Kubernetes sort pods by age
  • View object/property state in Xcode while debugging
  • ReferenceError: app is not definied in Node.js - SteamWebAPI
  • Combining keys and full text when working with gettext and .po files
  • How do I tell the maven-jaxb2-plugin to run xjc on all .xsd files in a maven dependency?
  • Capitalization using PHP
  • Kubernetes on Rasperry Pi kube flannel CrashLoopBackOff and kube dns rpc error code = 2
  • How can I identify a specific item in and add a delete button each row in jquery
  • How to get NSLayoutConstraint's identifier by Its pointer?
  • glog verbose logging & Boost program options
  • How do I only display one element and not two based on lowest InternalPartyID?
  • Update-rc.d custom script running too late, and also runs at shutdown
  • Access a nested object array
  • how to group array of nsdictionary according to the value inside the element
  • Parse “key cannot be nil” error on [PFObject saveInBackground] (Cocoa)
  • How to use boost program_options to read an integer array?
  • Prestashop : loop on $product->save() method
  • e2e testing angular 7: Failed: Cannot read property 'fetchData' of undefined
  • How to get same result as following Mysql query from Solr?
  • NSURLCredentialStorage default credential not used automatically
  • In GTK, how do I change the localized language text on the UI while it's running?
  • Thinking Sphinx and searching multiple models
  • App Script: How to view PDF of Google Sheet after function to save to Google Drive?
  • How to click a button on a web page that has no id?
  • CakePHP 2 How to pass variables through the URL when using pagination?
  • Pretty good heuristic evaluation rules for big TicTacToe 5x5 board
  • Receive .txt file from parse.com and place in textview
  • Keycloak: Access token validation end point
  • List Events on defined area using Google Maps
  • How to read/write integer values from bytes without old_io?
  • HDFS resiliency to machine restarts in DC/OS
  • cURL not working while fetching Google API data
  • Entity Framework and Predicate Builder - Predicates being Ignored in SQL Query
  • retrofit.RetrofitError: 404 Not Found
  • Any command in mysql equivalent to Oracle's autotrace for performance turning
  • Microsoft Visual Studio Community 2015 always crashes in Windows 10 if swithed to Visual FoxPro