2

I'm having a problem where a mysql query gets stuck in the "sending data" state, sometimes for upwards of 12-20 min, and then I see other queries stack up behind it until I run out of connections and the site goes down. It seems that they do finish eventually, if I sit and watch them long enough they go to "removing temp tables" and then they clear out.

Now I didn't write this code, but I'm tasked with fixing it. I do the IT and server admin stuff for our company, but I leave all the actual coding for the site up to my developer. I know next to nothing about mysql, but the SELECT query that my web guy is using looks fishy to me. If I am reading it right he is saying "look for product_id xxxx in every single table and give me those results, discard the rest" He claims that because the tables are indexed it isn't a problem to call data out in this way.

If I run the query from the mysql console it takes between 3 and 20 seconds to return results, and obviously longer when PHP actually calls it. Note that it is not limited to any one product ID. It does not seem to make much difference what product I'm trying to call.

This is what I see in mysql.log 

    47384 Connect   ecom_a@localhost on 
    47384 Init DB   ecom_Products
    47384 Query SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'ecom_Products'
    47384 Query 
SELECT * FROM ((SELECT * FROM `AC_Electric_Motor_Run_Capacitors`) 
UNION ALL (SELECT * FROM `AC_Electric_Motor_Start_Capacitors`) 
UNION ALL (SELECT * FROM `AC_Filters`) 
UNION ALL (SELECT * FROM `AC_Gear_Motors`) 
UNION ALL (SELECT * FROM `AC_Line_&_Load_Reactors`) 
UNION ALL (SELECT * FROM `AC_Voltage_Regulators`) 
UNION ALL (SELECT * FROM `Auxiliary_Contact_Blocks`) UNION ALL (SELECT * FROM `Bleed_Down_Resistors`) UNION ALL (SELECT * FROM `Coils`) UNION ALL (SELECT * FROM `Contactors`) UNION ALL (SELECT * FROM `Crimpers`) UNION ALL (SELECT * FROM `DC_Gear_Motors`) UNION ALL (SELECT * FROM `Diesel_Engines`) UNION ALL (SELECT * FROM `Disconnects`) UNION ALL (SELECT * FROM `Electric_Motor_Slide_Bases`) UNION ALL (SELECT * FROM `Electric_Motors`) UNION ALL (SELECT * FROM `Electric_Powered_Water_Pumps`) UNION ALL (SELECT * FROM `Electrical_Enclosures`) UNION ALL (SELECT * FROM `Engine_Powered_Water_Pumps`) UNION ALL (SELECT * FROM `External_Control_Interfaces`) UNION ALL (SELECT * FROM `Float_Switches`) UNION ALL (SELECT * FROM `Foot_Switches`) UNION ALL (SELECT * FROM `Generator_Heads`) UNION ALL (SELECT * FROM `Horizontal_Shaft_Gas_Engines`) UNION ALL (SELECT * FROM `Insulating_Resins`) UNION ALL (SELECT * FROM `Limit_Switches`) UNION ALL (SELECT * FROM `Magnet_Wire`) UNION ALL (SELECT * FROM `Manual_Transfer_Switches`) UNION ALL (SELECT * FROM `Mechanical_Interlock_Blocks`) UNION ALL (SELECT * FROM `Medium_Voltage_Transformers`) UNION ALL (SELECT * FROM `Motor_Starters`) UNION ALL (SELECT * FROM `Multi-fuel_Engines`) UNION ALL (SELECT * FROM `Overload_Relays`) UNION ALL (SELECT * FROM `PTO_Generator_3-Point_Hitch_Mounts`) UNION ALL (SELECT * FROM `PTO_Generator_Drive_Adapters`) UNION ALL (SELECT * FROM `PTO_Generator_Drive_Shafts`) UNION ALL (SELECT * FROM `PTO_Generator_Trailers`) UNION ALL (SELECT * FROM `PTO_Generators`) UNION ALL (SELECT * FROM `Packaged_Standby_Generators`) UNION ALL (SELECT * FROM `Portable_Generator_Covers`) UNION ALL (SELECT * FROM `Portable_Generator_Lifting_Provisions`) UNION ALL (SELECT * FROM `Portable_Generator_Wheel_Kits`) UNION ALL (SELECT * FROM `Portable_Generators`) UNION ALL (SELECT * FROM `Resilient_Vibration_Isolators`) UNION ALL (SELECT * FROM `Resistance_Wire`) UNION ALL (SELECT * FROM `Rotary_Frequency_Converters`) UNION ALL (SELECT * FROM `Rotary_Phase_Converters`) UNION ALL (SELECT * FROM `SO_Cable`) UNION ALL (SELECT * FROM `Single_Phase_Motors_with_Base_Mount`) UNION ALL (SELECT * FROM `Single_Phase_Motors_with_Face_&_Base_Mount`) UNION ALL (SELECT * FROM `Single_Phase_Motors_with_Face_Mount`) UNION ALL (SELECT * FROM `Soft_Starters`) UNION ALL (SELECT * FROM `Special_Metal_Wire`) UNION ALL (SELECT * FROM `Static_Frequency_Converters`) UNION ALL (SELECT * FROM `Static_Phase_Converters`) UNION ALL (SELECT * FROM `Surge_Suppressors`) UNION ALL (SELECT * FROM `Three_Phase_Motors_with_Base_Mount`) UNION ALL (SELECT * FROM `Three_Phase_Motors_with_Face_&_Base_Mount`) UNION ALL (SELECT * FROM `Three_Phase_Motors_with_Face_Mount`) UNION ALL (SELECT * FROM `Transformers_-_General`) UNION ALL (SELECT * FROM `Variable_Frequency_Drives`) UNION ALL (SELECT * FROM `Variable_Transformers`) UNION ALL (SELECT * FROM `Vehicle_&_Equipment_Batteries`) UNION ALL (SELECT * FROM `Vertical_Shaft_Gas_Engines`) UNION ALL (SELECT * FROM `Welding_Cable`) UNION ALL (SELECT * FROM `_Default`)) 
AS t WHERE product_ID = 'LF0009' LIMIT 1
Shawn Balestracci
  • 7,380
  • 1
  • 34
  • 52
Xytrix01
  • 23
  • 2
  • Any chance this is happening in a transaction? Locking all those tables at once could certainly be a problem... (What type of tables are they? MyISAM? InnoDB?) –  May 20 '13 at 20:38
  • 1
    Can you specify `product_ID` in each of the subqueries instead of sending it to the wrapper `SELECT`? – imthepitts May 20 '13 at 20:41
  • Sorry, Should have mentioned, it is MYISAM. – Xytrix01 May 20 '13 at 20:42
  • If you are able to simultaneously select ALL columns from ALL of those tables and return a result set that makes sense then they probably shouldn't be separate tables in the first place! – Strawberry May 20 '13 at 20:55

3 Answers3

3

Assuming it really is necessary to search each table, it would be better if each sub-query applied the WHERE clause. As it stands, this query will build one mega-result set over all the tables before applying the where and then limiting the result set. Maybe MySQL can optimise this, maybe it can't, but you could try:

....
UNION ALL (SELECT * FROM `AC_Filters` WHERE product_ID = 'LF0009')
UNION ALL (SELECT * FROM `AC_Line_&_Load_Reactors` WHERE product_ID = 'LF0009')
....

I suspect this query is being generated programmatically in PHP, given the first query that's run, so it shouldn't be difficult to make that change.

More broadly, from the looks of the query, you appear to have a table per product type, instead of e.g. a table called 'products' with an ID column for the type, and table listing the unique products.

Rory Hunter
  • 3,425
  • 1
  • 14
  • 16
  • I think that your last statement there nails it. We do indeed have a table for each different category of products, and I was told that due to our part # scheme it is necessary to search all the tables. For example, AT0001, FT0001, and MT0001 are all transformers so there is no good way from that info to tell it what table it needs. I will talk to him about changing the way this is laid out, ad there is no reason at all to be looking in the "pumps" table for transformer specs. – Xytrix01 May 20 '13 at 20:56
  • Yup, a table for products and a table for types. At least two indexes on products, one for the product ID, and one for the product type. – Rory Hunter May 20 '13 at 21:00
  • 1
    This should certainly make it much faster and lower the window of opportunity for a deadlock, but read-locking all those tables at once will likely still present an opportunity for deadlock when one of them was already write-locked and then other threads start wanting write-locks to other tables. see: http://stackoverflow.com/questions/6415195/myisam-place-table-lock-on-table-even-when-dealing-with-select-query –  May 21 '13 at 15:02
2

If the table structures of all those tables are identical, then they should probably be a single table with an extra field to distinguish AC_Gear_Motors from AC_Filters etc. The reason it looks like all the tables have identical fields is because all fields are being selected from each table and being UNIONed.

Additionally, look into identifying which fields you actually need, instead of using *.

Asad Saeeduddin
  • 46,193
  • 6
  • 90
  • 139
1

This will likely perform much better...

SELECT only, the, columns, i, actually, need
  FROM one_big_fat_properly_indexed_table t 
 WHERE product_ID = 'LF0009';

...oh, and LIMIT without ORDER BY makes little sense, so I left it off.

Strawberry
  • 33,750
  • 13
  • 40
  • 57
  • Actually, the `LIMIT` may well keep his code from dieing when it gets two result rows. Note: the column names and types don't have to match, just the total number of columns. see this SQL fiddle: http://sqlfiddle.com/#!2/928fd/8 –  May 21 '13 at 14:52