How to optimize your keys in MYSQL?

Guys, tell me what's wrong with the structure and how to optimize keys?

There are two tables:
1) `id`, `firma`, `product`, `price` (the key just `id` AI meaning not just number)
2) `id`, `firma`, `product`, `price` (similar)

Need to update table 1 based on table 2.

UPDATE `tab1` t1
INNER JOIN `tab2` t2
ON t1.`firma` = t2.`firma` 
AND t1.`product` = t2.`product` 
SET t1.`price` = t2.`price`


The update query is 50 seconds!!! And this despite the 22,000 records in one and 5,000 records in the second.

I don't really understand the principle of operation of composite keys. Perhaps it was he needed. There is just an INDEX is UNIQUE, it is also possible to group two fields in the 1 index. Advise how to be with the index?
July 2nd 19 at 17:08
2 answers
July 2nd 19 at 17:10
Solution
I recommend it to read
July 2nd 19 at 17:12
Solution
Your question is missing the solid part of the input data. What keys, where they are "is" how much is the sample why the INNER JOIN? and so on.

The keys (indexes) You have to stand on those fields involved in the condition of the sample, as in filtration. In your specific case is:
ON t1.`firma` = t2.`firma` AND t1.`product` = t2.`product`


that is, the keys (indexes) must be installed on the fields of company and product, however, if You want to achieve maximum speed when such operations - in addition to being on the sidelines of the company and product must be marked with the index type is KEY - it is highly desirable that if they were numbers, because with numbers machines work much faster.

In General, all objects that You here in this way trying to "glue" with each other should be a unique identifier, ID, GUID, or some other (your choice).

*Actual numbers depends on the situation of course, in some cases this can be for example GUID's, it is desirable that it was NOT the name of the firm, a La "OOO Horns and hoofs" and product names like "a Loaf of sliced #1".

Well, if You very well want to use the names (titles) instead uniquely identify each object, keep in mind that the fall in the index the first 255 characters. And index on text fields (CHAR/VARCHAR) and in itself will take more than its numeric counterpart, and as a result will run slower than if it were a number (object ID).
Thanks for the answer advanced. I have numeric indexes. I tried to make a separate index and a composite, and I do not understand these differences. In the sample used definitely these 2 fields. firma and produc. The difference in speed there is between the individual and the composite index.

But the problem is solved, the increase with the creation of these indexes has accelerated to 1 sec query 50, so I think the issue has been resolved for the first time. With regards to the GUID I have not yet studied, but the idea is good. - thelma commented on July 2nd 19 at 17:15
: about the GUID - query "SELECT UUID();" - returns the UUID/GUID is some unique identifier, sometimes it is useful to use it instead of ID. Especially in database of MySQL, where "out of the box" does not include such things as "sequence".

About composite indexes a composite index, in principle, not globally different from the usual, very simply put, You could write the date in one field and hang it on the index, and you could create 3 fields (year, month, day) and hang composite index on the 3 fields at once. If you delve into the intricacies of MySQL, the difference between them will be, but... I don't know how it is in short to explain within the "answer". Regarding the composite index, you can "quick" to give one piece of advice: if You have a sample (in conditions) necessarily involves two fields at once - can try to hang them on the composite index, if these fields are selected one at a time (or unable to participate in such a sample) - hang on each field in your index. In General, I think it is better not to bother with the intricacies of composite indices, in Your case, with a probability of 99% will fit perfectly ordinary. - Salvado commented on July 2nd 19 at 17:18
Once again thank You very much. Everything is very clearly written. Will deepen their knowledge. - thelma commented on July 2nd 19 at 17:21

Find more questions by tags MySQL