CODEWITHSUNDEEP
mysql
Xytrix01
Xytrix01

Reputation: 23

mysql query structure, is UNION ALL really the right way to be doing this?

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

Upvotes: 2

Views: 210

Answers (3)

Strawberry
Strawberry

Reputation: 33945

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.

Upvotes: 1

user1726343
user1726343

Reputation:

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 *.

Upvotes: 2

Rory Hunter
Rory Hunter

Reputation: 3470

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.

Upvotes: 3

Related Questions