How to speed up GROUP BY execution?

Question

PHP

Hello!
There are 700 tables on 5 records, each table weighs on 200 Mb.
The table structure is as follows:

CREATE TABLE `table` (
  `id` int(11),
  `id2` int(11),
  `id3` int(11),  
  `p1` int(11),
  `p2` int(11),
  KEY `id` (`id`)
) ENGINE=MyISAM;

The following query is applied to each of these tables:

SELECT `id1`, `id2`, `id3`, `p1`, COUNT(`p1`) AS `count`, `p2` 
FROM `table` 
GROUP BY `id1`, `id3`, `p1`, `p2`

The query result is sent by INSERT to another special table.
The problem is the query execution time, example 200 sec. Thus, it will take me 38 hours to process all 700 tables. I would like to reduce this time to 1-3 hours.
Question : Are there ways to reduce query time?
Measures taken:
1. In order not to drive query results traffic between servers, the php-script was placed on the database server.
TASK SOLVED
Thank you all for your participation, I used the GROUP_CONCAT () function, which reduced the sampling time by 4 seconds.

Answer 1

No indexes? Show the explain query.

Answer 2

Such a query cannot be applied to the table, you have fields in the select that do not participate in the grouping and without the use of aggregate functions.
Show explain query and show create tables.
Judging by the runtime, the indexes are missing or incorrectly placed. Tables without sharding?
ZY If you do not want to publicly shine with data, then you can personally, but already in the context of paid work.

Answer 3

Look at the query plan, add the missing indexes.

Answer 4

Does the data structure really not allow all these 700 tables to be reduced into one, adding several fields, if necessary, with additional. criteria? It's just awful, to design a database like that. Yes, and suddenly you need to update the data structure (read "tables") and then you need to add / delete a column in each of the 700 tables?

Answer 5

Try running mysqltuner.pl and see its recommendations for tuning mysql. Have you tried the index on all fields (`id1`, `id3`, `p1`, `p2`)?

Answer 6

Could you explain in words what the request should do? The query you wrote doesn't make sense.
If you need to get the number of unique combinations (id1, id2, id3, p2), then try this query:

SELECT `id1`, `id2`, `id3`, COUNT(*) AS `count`, `p2` 
FROM `table` 
GROUP BY `id1`, `id2`, `id3`, `p2`
ORDER BY NULL

If p2 is declared NOT NULL, then it should work fast enough.