Comparing huge data from 2 tables

Question

I'm using PHP and under localhost using Wamp. I have 2 tables from my database which has a huge data almost 13,000 plus each table. I want to check if NameFromA from TableA exist in NameFromB from TableB. I have this code working when I try to use small amount of data around 100 data.

SELECT * FROM TableA WHERE EXISTS (SELECT * FROM TableB WHERE  NameFromA = NameFromB)

My problem is when I try running it and comparing the 13,000 plus data nothing happens. It has no output.

May be it takes a lot of time to make such opeartion. From my point of view 13000 is very small amount of data. You can also redesing the query. — Bogdan Bogdanov, Aug 01 '15 at 13:41
@BogdanBogdanov can you help me redesign my query. I have no idea on how to do it. — mar, Aug 01 '15 at 13:44
13000 records is not "huge" by any means. Computers are very fast. — Sverri M. Olsen, Aug 01 '15 at 13:45
I post answer. Please check it because I primarily work with SQL Server and I am not sure for MySql syntax. — Bogdan Bogdanov, Aug 01 '15 at 13:46
The answers to [this question](http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins) may be useful to you (especially the Venn diagrams). — Sverri M. Olsen, Aug 01 '15 at 13:57

Bogdan Bogdanov · Answer 1 · 2015-08-01T14:16:09.887

1

Try this:

SELECT 
  TableA.* 
FROM 
  TableA 
  INNER JOIN TableB ON (TableA.NameFromA = TableB.NameFromB)

If it is still slow, may be you have problem in the DB or the timeout time is too short.

You can also try to run this in MySql management too to see how long that query will take.

One more thing. The data that you retreive must be send to web server. If your table contains a lot of data for each row and there are a lot of rows this also will take time.

EDIT:

Ok @mar. You have to make a little troubleshooting to find where is the problem.

First: if your PC is powerfull enought. Because for normal PC and Server 13k records are nothing.

Second: are you sure that there are at least one name from table A which is presented in table B?

Third: try to run query in external SQL tool - not in php. If it retunrs correct set quickly, then the problem is in php.

edited Aug 01 '15 at 14:16

answered Aug 01 '15 at 13:43

Bogdan Bogdanov

1,707
2
20
31

2

Shouldn't it be `...JOIN TableB ON (A.a = B.b)`? – Sverri M. Olsen Aug 01 '15 at 13:48
Unable to execute query. – mar Aug 01 '15 at 13:49
10x, @Sverri M. Olsen. I was in a hurry to explain answer so I made this very stupid mistake. – Bogdan Bogdanov Aug 01 '15 at 13:49
Sorry @mar. I fix it. – Bogdan Bogdanov Aug 01 '15 at 13:50
Answer of @Samir is also correct in general. But he must replace `*` with `a.*`. – Bogdan Bogdanov Aug 01 '15 at 13:52
@BogdanBogdanov by the way I'm using wamp. localhost only – mar Aug 01 '15 at 14:15
@BogdanBogdanov yes my pc is powerful enough and I'm sure that there is at least one present in tablea from tableb..third it gives me timeout – mar Aug 01 '15 at 14:23
Ok @mar. Run query in external editor to see how much it is running. Based on that you can try fix your problem. In general the amount of data is very small and the query is very simple. May be there are some specific indexes or configuration in your MySql DB which confise execution of the query (if it is in the query). – Bogdan Bogdanov Aug 01 '15 at 14:26

Samir Selia · Answer 2 · 2015-08-01T13:55:08.370

1

Create index on column NameFromA in TableA and NameFromB in TableB.

Try below query:

SELECT a.* 
FROM TableA AS a, TableB AS b
WHERE a.NameFromA = b.NameFromB

edited Aug 01 '15 at 13:55

answered Aug 01 '15 at 13:49

Samir Selia

7,007
2
11
30

Replace `*` with `a.*`. The question requires to return data only from table `a`. – Bogdan Bogdanov Aug 01 '15 at 13:54
@Samir by the way I'm using wamp. localhost only – mar Aug 01 '15 at 14:04

Comparing huge data from 2 tables

2 Answers2