1

Topic: How to make join two big tables?

The first table 20 of million the records, the second 65 records. In the first there are no unique records and it has 7 fields. The second table on one field, all records are unique. Normal join probably does not approach.... What to do?

2

Re: How to make join two big tables?

wissem wrote:

it has 7 fields. The second table on one field

Structures in studio.

wissem wrote:

Normal join probably does not approach

?

3

Re: How to make join two big tables?

The first table:
mdlnumber sub_code supplier_name weight_val weight_type price currency purity
MFCD00000014 AK176157 ARKPHARMINC 500 G 390 USD 95+%
MFCD00000014 AT-22094 ATOMOLE 1000 G 340 USD 98.0%
MFCD00000014 U65676 AURUMPHARMA 25 G 129 USD 98%
MFCD00000014 U65676 AURUMPHARMA 500 G 613 USD 98%
MFCD00000014 U65676 AURUMPHARMA 1 KG 1042.1 USD 98%
MFCD00000014 FB60350 CARBOSYNTH 25 G 55 USD
MFCD00000014 FB60350 CARBOSYNTH 50 G 94 USD
MFCD00000014 FB60350 CARBOSYNTH 100 G 160 USD
MFCD00000014 FB60350 CARBOSYNTH 250 G 320 USD
MFCD00000014 FB60350 CARBOSYNTH 500 G 544 USD
MFCD00000014 CML-22215 CHEMIELIVA 1 G 402 USD 99%
MFCD00000014 CML-22215 CHEMIELIVA 2 G 570 USD 99%
MFCD00000014 QD-4778 COMBI-BLOCKS 1 G 20 USD 97%
The second
id f_number
1 F9994-5419
2 F9994-5415
3 F9994-5414
4 F9994-5156
5 F9994-3234
6 F8881-8992
7 F1905-8728

4

Re: How to make join two big tables?

and how to interpose normally given that it was clear?

5

Re: How to make join two big tables?

wissem wrote:

how to interpose normally given that it was clear?

to Fulfil on the console

SHOW CREATE TABLE <a table name>;

and to copy here result.
Data - does not interest. But for help - we use tag CSV (button Table), and record field values we divide commas. Also we use the button Review to be convinced that turned out as it is necessary.

6

Re: How to make join two big tables?

Akina wrote:

the Data - does not interest.

And what is necessary?

7

Re: How to make join two big tables?

The first table
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| prices_in_usd | CREATE TABLE ` prices_in_usd ` (
` mdlnumber ` varchar (12) NOT NULL;
` sub_code ` varchar (32) NOT NULL;
` supplier_name ` varchar (32) NOT NULL;
` weight ` varchar (2) NOT NULL;
` price ` decimal (32,2) NOT NULL;
` currency ` varchar (3) NOT NULL;
` usdprice ` decimal (32,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------
The second table.
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| f_numbers | CREATE TABLE ` f_numbers ` (
` id ` int (11) NOT NULL AUTO_INCREMENT;
` f_number ` varchar (32) NOT NULL;
PRIMARY KEY (` id `)
) ENGINE=InnoDB AUTO_INCREMENT=69867 DEFAULT CHARSET=utf8 |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

8

Re: How to make join two big tables?

Now still specify fields on which correspondence is installed...

9

Re: How to make join two big tables?

t1.sub_code = t2.f_number

10

Re: How to make join two big tables?

Well actually if to create on this field indexes

CREATE INDEX idx_sub_code ON prices_in_usd (sub_code);
CREATE/* UNIQUE */INDEX idx_f_number ON f_numbers (f_number);

That the request simply departs... Certainly that this string field - is bad, but not a noise.
In general, I do not see while  to usage JOIN (though in an any way it is necessary to use it even if to do it implicitly).

11

Re: How to make join two big tables?

wissem;
And what sense in the second table if on an output JOIN the contents equal to a field t1.sub_code goes, and except t2.f_number there is not present other significant fields?

12

Re: How to make join two big tables?

miksoft wrote:

what sense in the second table

it is possible, values are imported to this table for a filtration - push 65 values in the request text  it can to appear inconveniently...

13

Re: How to make join two big tables?

Quite right, for a filtration. From the first table those records where sub_code it is equal f_number from the second are necessary to us only that strongly reduces the first table. Many thanks for helps, in hour or so I will try and I will unsubscribe.

14

Re: How to make join two big tables?

Akina, many thanks! You very much helped me smile