A suggestion is that you post the create table statements also. That way we can all easily create the same tables and we can also directly see which indexes you have created.
And if you want to rewrite your query before posting it, you should check better for spelling errors etc before posting it.

But my suggestions are:
1.
Do you actually need DISTINCT?
Isn't the records unique withing table1 already?

2.
Your original query should look like this:

SELECT table1.*FROM table1INNER JOIN resttyperest ON ( table2.table1_id=table1.id AND table2.table3_id IN (6, 10, 15) )ORDER BY restaurant.name


3.
And then I suggest index:
table2(table3_id, table1_id)
which combined with the primary key id for table1 should find the records very fast. It would still need the filesort.

4
But possibly with index:
table1(name, id))
you should be able to avoid the filesort also.

Hi Sterin...

the correct SQL queries follows:


SELECT DISTINCT table1.*FROM table1 INNER JOIN table2 ON (table2.table1_id=table1.id)WHERE table2.table3_id IN (6, 10, 15)ORDER BY table1.name


The query was rewritten to:

SELECT table1.*FROM table1WHERE (SELECT COUNT(id) FROM table2 WHERE table2.table3_id IN (6, 10, 15) AND table2.table1_id=table1.id ) > 0ORDER BY table1.name


1) Yes, I need DISTINCT in the first query. Why? Because one record from table1 can be "joined" with many rows in "table2". But I want to get a row from table1 only once (and only if it fulfills conditions from table2). Without DISTINCT I get:
id | name
1 | Name1
1 | Name1
1 | Name1
5 | Name5
5 | Name5
...

2) I tried to move that condition from WHERE clause to ON clause, but it seems to be considered as the same query in MySQL (time spent on this query is equal and EXPLAIN gives the same result)

3) Currently I have the indices on:

a) table1(id) b) table1(name) c) table2(table1_id) d) table2(table3_id) e) table2(id, table1_id, table3_id) f) table3(id)


Do you think, that I should add table2(table3_id, table1_id)? Maybe it is in a duplicity to indices c) and d).

4) Yes, the same question: I already have table1(id) and table1(name). Could your index improve performance of that query?

Mysql will only use one index from each table in a query - so your indexes (c) and (d) are not the same as having the two column index sterin suggests.

That two column index will probably make your original query fast enough. However, I think to avoid a filesort, the table1 index will need to be (id,name) rather than (name,id) - mysql will need the ID to join on before it uses the name to order by.