How to optimize SQL query?

Question

SQL

Hello everyone
Tell me, is it necessary to optimize the SQL query in terms of server load?
Or it is normally made and so and should be carried out?
We have two tables
numbers - for example, a list of phone numbers numbers_type - categories assigned to each number
(there may be several)
Task
Select from numbers numbers with certain categories (several) ` FROM `numbers_type` WHERE `type` IN (1, 3, 5) ) I have a question Subquery returns a large number of records For example 50,000 items Then this resulting selection falls into the main query
I don’t know how to say it correctly - does MySQL digest it normally?
Or is it better to somehow make a different structure of the labels so that the request is easier to execute for the server
Thanks for the answers

Answer 1

It seems to me that JOIN will work better for you here

SELECT 
  *
FROM 
  `numbers` A 
  LEFT JOIN `numbers_type` B ON A.`number` = B.`number` 
WHERE 
  B.`type` IN (1, 3, 5)

Answer 2

50k - a meager sample, forget it !!))
if the project is for growth, then don’t score it !!))
In your version, the sample is ridiculous, you haven’t seen 10 storey joins, colleague !!...
And also - don’t forget that the database is ALWAYS stored on the disk, and in memory it appears only later)))! hence the output for some huge queries - the issue is resolved faster by processing not in the database, but on the application.
In short, you make a selection of ALL your numbers without conditions - for the database this is the most non-gluttonous operation, in fact two moments, then the selection into an array and already on the server you parse what and how, through the same php. Tested on samples 20M-300M - - filtration speed is two orders of magnitude higher if this task is entrusted to the application!

Answer 3

How about join? Yours looks very strange.
50 thousand is not much.
in has limits https://stackoverflow.com/questions/1069415/limit-...

Answer 4

when you do a join
on indexed fields, of course,
the muscle runs along the lines (and on disk the table is a regular binary file) by a banal comparison of strings (smoke algorithms if necessary) - consider it the fastest operation, and bring into memory all the appropriate ones (therefore, you need to select not * but specific fields - less data, less hanging in memory)
while indexes remove the need to go through obviously non-matching lines
when you use where in - then the result of the IN muscle also has to be kept in memory, but at least only those in where will have to be checked , and with an unsuccessful optimizer - the entire table, and with a very unsuccessful optimizer - more than once
to put it simply, "where in" is always slower than join, in fact it is syntactic sugar for one-time requests,
if it seems faster, then on small sets, where join has more overhead