MySQL indexes in BINARY (16). What size?

Question

I have a table with over 6.6 million rows.

I got a field called trip_id which is in BINARY(16) . I find my request too slow ( 0.2 seconds ). This query is executed approximately every 3 seconds.

Before doing something stupid, I want to know if I can reduce the size of the index by trip_id from full to 12, will it make a difference?

If I try to change my request more, will it change?

thanks

EDIT:

Inquiry:

 SELECT stop_times.stop_id FROM trips LEFT JOIN stop_times ON trips.trip_id = stop_times.trip_id WHERE trips.route_id = '141' GROUP BY stop_times.stop_id ORDER BY trips.trip_headsign ASC, stop_times.stop_sequence ASC 

trip_id BINARY(16)

route_id SMALLINT(3)

trip_headsign VARCHAR(50)

stop_sequence SMALLINT(3)

Request Explanation:

Answer 1

After doing the research, I found the problem, because yes, 0.2 seconds slower.

 SELECT t.trip_headsign, st.stop_sequence, s.stop_code, s.stop_name FROM stop_times AS st JOIN stops AS s USING (stop_id) JOIN ( SELECT trip_id, route_id, trip_headsign FROM trips WHERE route_id = '141' LIMIT 2 ) AS t WHERE t.trip_id = st.trip_id GROUP BY st.stop_id 

First, instead of executing a LEFT JOIN here JOIN is faster. But an important point I compared all the results of the trips in the WHERE instruction.

However, since the bus can have only 2 directions, I need to limit my results to 2. Now my results are approaching 0.018. Over 1000% improvement.

Answer 2

You have "Using Temporary" and "Using File Management" in the "Advanced" column.

These are sure signs that you can improve the situation. The reason they appear is related to your GROUP and ORDER clauses.

First step: are they really needed? You may find that, in the end, it’s cheaper to sort them with a language that consumes this data.

Second step: if you still need ORDER BY , look at ORDER BY Optimization in MySQL docs. The reason the index is not used for sorting here is because the different GROUP BY and ORDER BY clauses.

Think outside the box. You do not do any aggregation, so perhaps the grouping is not needed. Maybe just pull all the lines and then ignore duplicate identifiers.

Answer 3

Try adding trip_headsign to your route index. Since you are using this in ORDER BY, mysql must go to the actual table to get it for every entry found in the index that matches route_id. If you do not see the “Use Index” in the “Advanced” column of the explanation, this means that MySQL is forced to revert to the actual table to get additional information.