SilviuT SilviuT - 5 days ago 5
MySQL Question

Group By MariaDB very slow

I'm using MariDB 10.1.18 under Linux.

I have a simple table (t) with the following structure:

| id | a | b | c |
-------------------
| 1 | 3 | 7 | 10 |
| 2 | 4 | 6 | 9 |
| 3 | 2 | 7 | 11 |
| 4 | 3 | 5 | 10 |
| 5 | 4 | 8 | 12 |
| 6 | 2 | 9 | 6 |


id is primary key
a - has BTREE index
b - has HASH index
c - has HASH index


I assume the Primary key gets indexed automatically.
My query is simple:

SELECT * FROM t GROUP BY a


For performance purposes the ENGINE USED is
MEMORY
.

On 5 million rows, the above query takes 1 second to complete and utilizes the thread of one CPU to 100%. There are about 150 unique values for column a right now.

I assumed that this could be solved if I used loose index search. Unfortunately this does not seem to work in MariaDB as it is never used. loosescan is set to on.

I have tried

SELECT MAX(a) FROM t GROUP BY a


Which takes 1.1 seconds on my database.

Question is, how can I make this select blazing fast? Like 0.05 seconds.

Thank you!

Answer

So after a lot of work and testing this is the fastest solution so far:

  1. Use Memory Engine - it's at least 10 times faster than InnoDB stored on RAMDISK

  2. Make separate queries for every "a" column element instead of using Group BY and combine the results in PHP
    Ex. SELECT id FROM t WHERE b IN (3,4,5) AND c IN (6,7,8) AND a=1;

  3. Set composite Index for each column like this INDEX ON (a,b) , INDEX ON (a,c) to give the planner enough flexibility for any type of query. INDEXES have to be BTREE.

A very complex query on a 5 mil row table now takes about 0.35 seconds.

Comments