select statment hangs on sending data for big table

Description

In some environments with large amounts of data we seen hanging statement on "sending data" state.
For example for table

Table

Non_unique

Key_name

Seq_in_index

Column_name

Collation

Cardinality

Sub_part

Packed

Null

Index_type

Comment

Index_comment

piwik_log_link_visit_action

0

PRIMARY

1

idlink_va

A

2

NULL

NULL

BTREE

piwik_log_link_visit_action

1

index_idvisit

1

idvisit

A

NULL

NULL

NULL

YES

BTREE

piwik_log_link_visit_action

1

index_idsite_servertime

1

idsite

A

NULL

NULL

NULL

BTREE

piwik_log_link_visit_action

1

index_idsite_servertime

2

server_time

A

NULL

NULL

NULL

BTREE

piwik_log_link_visit_action

1

transitions_url

1

idaction_url

A

NULL

NULL

NULL

YES

BTREE

piwik_log_link_visit_action

1

transitions_url

2

idsite

A

NULL

NULL

NULL

BTREE

piwik_log_link_visit_action

1

transitions_url

3

server_time

A

NULL

NULL

NULL

BTREE

piwik_log_link_visit_action

1

transitions_url_ref

1

idaction_url_ref

A

NULL

NULL

NULL

YES

BTREE

piwik_log_link_visit_action

1

transitions_url_ref

2

idsite

A

NULL

NULL

NULL

BTREE

piwik_log_link_visit_action

1

transitions_url_ref

3

server_time

A

NULL

NULL

NULL

BTREE

query below performed manually returns the result immediately, but from app hanging for 20 secons on sending data state

on larger databases (3 billions records and more) the same simple query hanging already for several minutes

The same situation occurred on different hardware , system (debian,redhat) and database (mariadb,percona) configurations.

Environment

None

Assignee

George Lorch

Reporter

Jacek Lubzinski

Labels

External issue ID

None

Freshdesk Tickets

None

Priority

Major
Configure