Styjun

I need to make this query:

SELECT min(d.id) id, p.SERIAL serial, p.pos_id pos_id, MIN(fecha) fecha
FROM pvs p
JOIN devices d
ON d.SERIAL=p.SERIAL
GROUP BY p.SERIAL, p.pos_id
ORDER BY p.pos_id ASC;

The issue is that "pvs" is a very long table with plus 3 million rows. It's a table where I upload raw data but then I try to get the id of these elements in the working database (hence the min(d.id) part). that's also the reason why I didn't add a foreign key as such on pos_id in pvs.

The structure is the following:

CREATE TABLE pvs (
 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `pos_id` INT(11) NOT NULL,
 `estado` VARCHAR(255) NOT NULL COLLATE 'utf8mb4_unicode_ci',
 `fecha` DATETIME NOT NULL,
 `serial` VARCHAR(255) NOT NULL COLLATE 'utf8mb4_unicode_ci',
 `created_at` TIMESTAMP NULL DEFAULT NULL,
 `updated_at` TIMESTAMP NULL DEFAULT NULL
)

CREATE TABLE devices (
 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `serial` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
 `rotulo` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
 `code` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
 `cost_center` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
 `guia_recepcion` INT(11) NULL DEFAULT NULL,
 `guia_reversa` INT(11) NULL DEFAULT NULL,
 `pep` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
 `modified_by` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
 `modified_on` DATETIME NULL DEFAULT NULL,
 `fecha_recepcion` DATETIME NULL DEFAULT NULL,
 `fecha_instalacion` DATETIME NULL DEFAULT NULL,
 `fecha_reversa` DATETIME NULL DEFAULT NULL,
 `status_id` INT(10) UNSIGNED NOT NULL,
 `location_id` VARCHAR(255) NOT NULL COLLATE 'utf8mb4_unicode_ci',
 `customer_id` INT(10) UNSIGNED NOT NULL,
 `str_id` INT(10) UNSIGNED NOT NULL,
 `model_id` INT(10) UNSIGNED NOT NULL,
 `pos_id` INT(11) NOT NULL,
 `user_id` INT(10) UNSIGNED NOT NULL,
 `technician_id` INT(10) UNSIGNED NOT NULL,
 `created_at` TIMESTAMP NULL DEFAULT NULL,
 `updated_at` TIMESTAMP NULL DEFAULT NULL,
 `serial_prev` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
 `reversa_prev` DATETIME NULL DEFAULT NULL
)

Should I make p.serial a foreign key even though it's a varchar? If I just use p.pos_id as join clause "ids" are the same. I grouped it with min() because I needed to add it to the select without adding it to the "group by". I need the same columns for the query.

EDIT:

be sure you have proper index

 create index idx1 on pvs( SERIAL); 

and

 create index idx2 on devices ( SERIAL);