MySQL How to efficiently compare multiple fields between tables?

Question

So my expertise is not in MySQL so I wrote this query and it is starting to run increasingly slow as in 5 minutes or so with 100k rows in EquipmentData and 30k or so in EquipmentDataStaging (which to me is very little data):

   CREATE TEMPORARY TABLE dataCompareTemp 
   SELECT eds.eds_id FROM equipmentdatastaging eds
   INNER JOIN equipment e ON e.e_id_string = eds.eds_e_id_string
   INNER JOIN equipmentdata ed ON e.e_id = ed.ed_e_id 
       AND eds.eds_ed_log_time=ed.ed_log_time 
       AND eds.eds_ed_unit_type=ed.ed_unit_type
       AND eds.eds_ed_value = ed.ed_value

I am using this query to compare data rows pulled from a clients device to current data sitting within their database. From here I take the temp table and use the ID's off it to make conditional decisions. I have the e_id_string indexed and I have e_id indexed and everything else is not. I know that it looks stupid that I have to compare all this information, but the clients system is spitting out redundant data and I am using this query to find it. Any type of help on this would be greatly appreciated whether it be a different approach by SQL or MySql Management. I feel like when I do stuff like this in MSSQL it handles the requests much better, but that is probably because I have something set up incorrectly.

Where do the fields `ed_e_id`, `eds_e_id_string`, etc, come from? It would be clearer to see what's going on here if you used table aliases and explicit references. — imm, Jul 05 '12 at 02:59
To be fair the reason I named the tables and fields with the conventions I did is so that I do not have to worry about aliasing and so that it is more clear. So for instance eds stands for EquipmentDataStaging, and ed stands for EquipmentData, and E stands for Equipment. Therefore you can extrapolate the joins from the naming convention. I appreciate you taking a look at this for me! I hope I made this clear. — shibbybird, Jul 05 '12 at 03:05
**tip:** you must index those columns which are using either with `WHERE` or with `ON` condition — xkeshav, Jul 05 '12 at 04:31

score 1 · Accepted Answer · edited May 23 '17 at 10:34

1

TIPS

index all necessary columns which are using with ON or WHERE condition here you need to index eds_ed_log_time,eds_e_id_string, eds_ed_unit_type, eds_ed_value,ed_e_id,ed_log_time,ed_unit_type,ed_value
change syntax to SELECT STRAIGHT JOIN ... see more reference

edited May 23 '17 at 10:34

Community

1
1

answered Jul 05 '12 at 04:38

xkeshav

53,360
44
177
245

I re-indexed the tables by dropping and rebuilding the tables. Seems like I am having shotty results. If I get this sorted out by rebuilding the DB with the new indexes which I will be able to do this weekend then I will accept the answer. Also I am definitely not using the straight join correctly either because it is taking way longer when I change the syntax. Thanks! – shibbybird Jul 06 '12 at 02:58
I'm going to mark this as the correct answer because it was due to my Indexes. I don't totally understand how MySQL does Indexing but I can assure you if you over index and you have foreign key constraints there can be worse slowing. I wish I received a more thorough answer, but diEcho is right to play around with the Indexes. – shibbybird Jul 12 '12 at 03:19

MySQL How to efficiently compare multiple fields between tables?

1 Answers1