Re: SHOW CREATE TABLEs

[Rick James]

An InnoDB really needs a PRIMARY KEY. A PK must be UNIQUE. I see not PKs, nor even any UNIQUE keys. And I can't really guess what is going on because of the terse field names. But, here goes some guessing...

GroupStructure smells like a many-to-many mapping between two things. If so, then there should be no duplicates in the table. In which case, I would recommend
PRIMARY KEY (gc, sgc)
INDEX(sgc, gc)
The secondary index lets lookups happen in the other direction.

I am not a fan of "prefix indexes:
KEY `svd` (`vd`(8))
-->
INDEX(vd)

Three tables are JOINed on vcc, yet only one has an INDEX on vcc. This may be fine, or it may be inhibiting optimization. Ditto for some of the other fields in the JOINs.

This would let SegmentsVCG2LU start the inner JOIN, and be "Using index":
INDEX(vd, vcc)
(The optimizer may want to use it, or may choose something else.)

Why do you mention VehicleContexts at all? Perhaps only to make sure ec=45? If ec is selective, then
INDEX(ec, vcc)

Groups might benefit from
INDEX(gl, gc) -- in that order.

So...
* NOT NULL, where appropriate.
* Add suggested indexes
* Add PRIMARY KEYs where the data is expected to be UNIQUE
* innodb_buffer_pool_size = 70% of _available_ RAM
* (for my sake -- and for your sake next year!) use meaningful names for fields.