I can not increase the speed of the request

JOIN construction increases the processing time of a request with a large number of records (well, it depends on the conditions).

 SELECT clients.*, max(orders.date) -- вывести максимальную дату, она же последняя (желательно еще там время учитвать, вдруг в 1 день несколько клиентов брали заказы) FROM clients c, orders o WHERE c.id = o.client_id GROUP BY client_id ORDER BY orders.date DESC 

or another option for the maximum order ID - it will also be the last one:

 SELECT clients.name, max(orders.id) -- вывести максимальную дату, она же последняя (желательно еще там время учитвать, вдруг в 1 день несколько клиентов брали заказы) FROM clients c, orders o WHERE c.id = o.client_id GROUP BY clients.name ORDER BY orders.date DESC 

Add in the client a link to his last order - see the difference. It really makes the server easier and reduces the response time.

Added by:

Solution (if the requests are not satisfied at the moment with the server that exists): transfer the old "unnecessary" (to this important request) data to the archive table, all the time work with the operational table (current year). For all other data consumers (who need everything) - make a View (view) and “hide” these old and operational data - that is, make a common interface that will hide the physical database structure, but will not affect the client’s functionality (the client will communicate with a view, but the one who wants to get the current data from a small tablet will get it directly).

Regardless of speed, you can't write like this:

 SELECT * … GROUP BY client_id 

According to the SQL rules for grouping, it is permissible to specify in the SELECT phrase fields from the GROUP BY + phrase aggregate functions from other fields. MySQL with some settings permits liberties, but this (a) can break down when transferred to another server and (b) gives an unwarranted result.

This task can be solved through a subquery, as it seems to me, with good performance:

 SELECT c.* FROM `clients` AS c INNER JOIN ( SELECT DISTINCT `client_id` FROM `orders` WHERE `date` >= '2015-01-01' ) AS co USING(`client_id`) 

(Here, instead of SELECT DISTINCT ... in the subquery you can apply ... GROUP BY client_id, will give the same result.)

https://dev.mysql.com/doc/refman/5.0/en/sql-mode.html#sqlmode_only_full_group_by

Edited : I carefully re-read the question and realized that I need to add a date to the subquery. With the advent of the aggregate function from GROUP BY is no longer dodge :)

 SELECT co.`max_date`, c.* FROM `clients` AS c INNER JOIN ( SELECT `client_id`, MAX(`date`) AS `max_date` FROM `orders` WHERE `date` >= '2015-01-01' GROUP BY `client_id` ) AS co USING(`client_id`) 

And the client_id field client_id to which table? If this is clients.client_id , then it is better to use the client field from the orders table. Accordingly, the index for the orders.client_id , orders.date (most likely the best composite one) should be in the orders table.

It is important that all sampling operations take place on the same table.

 SELECT clients.*, orders.date FROM clients LEFT JOIN orders USING(index_in_table_orders) WHERE orders.date >= '2015-01-01' GROUP BY orders.client_id ORDER BY orders.date DESC;