StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL Query Performance - Query/Schema/Indexes?
primarykey
Id
19408164
data
AcceptedAnswerId
0
AnswerCount
6
ClosedDate
CommentCount
5
CommunityOwnedDate
CreationDate
2013-10-16T15:58:16.390
FavoriteCount
1
LastActivityDate
2013-11-05T10:29:37.600
LastEditDate
2013-11-05T10:29:37.600
LastEditorUserId
1209020
OwnerUserId
1209020
ParentId
0
PostTypeId
1
Score
6
ViewCount
263
LastEditorDisplayName
text
Body
Basically having some performance issues with queries, mainly to my largest table which holds call data. The main query contains quite a few left joins & sub-selects, but in a scenario where I'm running a query where I expect back 1.3M calls to be returned, the query is just not doing it. Having to stop it at 7 minutes means there's definately a problem somewhere. I've narrowed down the main query and tested the simplest sub-select join which is <pre><code>SELECT DateStart, ID, NumbID, EffectiveFlag, OrigNumber FROM calls WHERE DateStart <= '2013-12-31' AND DateStart >= '2013-01-01' AND CallLength >= '00:00:00' AND Direction = '1' AND CustID IN (474,482,250,268,197,604,132,359,279,441,118,448,152,133,380,162,249,679,226,259,2450,2408,2451,2453,2439,2454,2444,2445,2452) </code></pre> And even that query takes 4.5s - so when it's a sub-select in a query with other joins & sub-selected, I can imagine why the query as a whole is unusable. The explain statement for the above query is <pre><code>+----+-------------+-------+-------+-------------------------------------------------------------------------------------------------------+----------------------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+-------------------------------------------------------------------------------------------------------+----------------------+---------+------+---------+-------------+ | 1 | SIMPLE | calls | range | idx_CustID,idx_DateStart,idx_CustID_DateStart,idx_CustID_TermNumber,idx_Direction | idx_CustID_DateStart | 7 | NULL | 1660009 | Using where | +----+-------------+-------+-------+-------------------------------------------------------------------------------------------------------+----------------------+---------+------+---------+-------------+ </code></pre> The database schema of the calls table is <pre><code>+-------------------+-------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+-------------+------+-----+---------------------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | CustID | int(11) | NO | MUL | 0 | | | CarrID | int(11) | NO | MUL | NULL | | | TariID | int(11) | NO | MUL | 0 | | | CarrierRef | varchar(30) | NO | MUL | | | | NumbID | int(11) | NO | MUL | 0 | | | VlviID | int(11) | NO | MUL | NULL | | | VcamID | int(11) | NO | MUL | NULL | | | SomeID | int(11) | NO | MUL | NULL | | | VlnsID | int(11) | NO | MUL | NULL | | | NGNumber | varchar(12) | NO | | | | | OrigNumber | varchar(16) | NO | MUL | NULL | | | CLIRestrictedFlag | int(2) | NO | | NULL | | | OrigLocality | varchar(11) | NO | MUL | | | | OrigAreaCode | varchar(11) | NO | MUL | | | | TermNumber | varchar(16) | NO | MUL | NULL | | | BatchNumber | varchar(10) | NO | MUL | | | | DateStart | date | NO | MUL | 0000-00-00 | | | DateClear | date | NO | | 0000-00-00 | | | TimeStart | time | NO | | 00:00:00 | | | TimeClear | time | NO | | 00:00:00 | | | CallLength | time | NO | | 00:00:00 | | | RingLength | time | NO | | 00:00:00 | | | EffectiveFlag | smallint(1) | NO | MUL | NULL | | | UnansweredFlag | smallint(1) | NO | MUL | NULL | | | EngagedFlag | smallint(1) | NO | | NULL | | | RecID | int(11) | NO | MUL | NULL | | | CreatedUserID | int(11) | NO | | 0 | | | CreatedDatetime | datetime | NO | MUL | 0000-00-00 00:00:00 | | | Direction | int(1) | NO | MUL | NULL | | +-------------------+-------------+------+-----+---------------------+----------------+ </code></pre> The indexes on the calls table are <pre><code>+-------+------------+---------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------+------------+---------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+ | calls | 0 | PRIMARY | 1 | ID | A | 23905312 | NULL | NULL | | BTREE | | | calls | 1 | idx_CustID | 1 | CustID | A | 1685 | NULL | NULL | | BTREE | | | calls | 1 | idx_NumbID | 1 | NumbID | A | 37765 | NULL | NULL | | BTREE | | | calls | 1 | idx_OrigNumber | 1 | OrigNumber | A | 5976328 | NULL | NULL | | BTREE | | | calls | 1 | idx_OrigLocality | 1 | OrigLocality | A | 45019 | NULL | NULL | | BTREE | | | calls | 1 | idx_OrigAreaCode | 1 | OrigAreaCode | A | 846 | NULL | NULL | | BTREE | | | calls | 1 | idx_TermNumber | 1 | TermNumber | A | 232090 | NULL | NULL | | BTREE | | | calls | 1 | idx_DateStart | 1 | DateStart | A | 4596 | NULL | NULL | | BTREE | | | calls | 1 | idx_EffectiveFlag | 1 | EffectiveFlag | A | 2 | NULL | NULL | | BTREE | | | calls | 1 | idx_UnansweredFlag | 1 | UnansweredFlag | A | 2 | NULL | NULL | | BTREE | | | calls | 1 | idx_EngagedFlag | 1 | UnansweredFlag | A | 2 | NULL | NULL | | BTREE | | | calls | 1 | idx_TariID | 1 | TariID | A | 110 | NULL | NULL | | BTREE | | | calls | 1 | idx_CustID_DateStart | 1 | CustID | A | 1685 | NULL | NULL | | BTREE | | | calls | 1 | idx_CustID_DateStart | 2 | DateStart | A | 919435 | NULL | NULL | | BTREE | | | calls | 1 | idx_NumbID_DateStart | 1 | NumbID | A | 37765 | NULL | NULL | | BTREE | | | calls | 1 | idx_NumbID_DateStart | 2 | DateStart | A | 5976328 | NULL | NULL | | BTREE | | | calls | 1 | idx_RecID | 1 | RecID | A | 288015 | NULL | NULL | | BTREE | | | calls | 1 | idx_CarrierRef | 1 | CarrierRef | A | 7968437 | NULL | NULL | | BTREE | | | calls | 1 | idx_CustID_CallTermNumber | 1 | CustID | A | 1685 | NULL | NULL | | BTREE | | | calls | 1 | idx_CustID_CallTermNumber | 2 | TermNumber | A | 246446 | NULL | NULL | | BTREE | | | calls | 1 | idx_CreatedDatetime | 1 | CreatedDatetime | A | 771139 | NULL | NULL | | BTREE | | | calls | 1 | idx_Direction | 1 | Direction | A | 2 | NULL | NULL | | BTREE | | | calls | 1 | idx_VlviID | 1 | VlviID | A | 50539 | NULL | NULL | | BTREE | | | calls | 1 | idx_SomeID | 1 | SomeID | A | 30 | NULL | NULL | | BTREE | | | calls | 1 | idx_VcamID | 1 | VcamID | A | 64 | NULL | NULL | | BTREE | | | calls | 1 | idx_VlnsID | 1 | VlnsID | A | 191 | NULL | NULL | | BTREE | | | calls | 1 | idx_CarrID | 1 | CarrID | A | 4 | NULL | NULL | | BTREE | | | calls | 1 | idx_BatchNumber | 1 | BatchNumber | A | 271651 | NULL | NULL | | BTREE | | +-------+------------+---------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+ </code></pre> Something which I understand may be causing the performance, is the indexes on columns with a low cardinality. I know columns such as Direction which has a cardinality of 2 is actually probably worse of with an index in terms of performance, but that alone shouldn't be making the statement so slow. In terms of the cardinality requirements to have a worthwhile index, is there a general cardinality percentage compared to total table records at which an index increases performance and when it reduces performance? I understand that no one is going to be able to throw an answer at me that will change the query time from 4.5s to 0.01s, but any advice on either the query itself, the table schema, the indexes, or the hardware would be greatly appreciated. Update: @Sebas "could you please rerun the query AND explain plan without the part: AND CallLength >= '00:00:00' AND Direction = '1' please?" <pre><code>+----+-------------+-------+-------+---------------------------------------------------------------------+----------------------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------------------------------------------------------------+----------------------+---------+------+--------+-------------+ | 1 | SIMPLE | calls | range | idx_CustID,idx_DateStart,idx_CustID_DateStart,idx_CustID_TermNumber | idx_CustID_DateStart | 7 | NULL | 724813 | Using where | +----+-------------+-------+-------+---------------------------------------------------------------------+----------------------+---------+------+--------+-------------+ </code></pre>
Tags
<mysql><sql><performance>
Title
MySQL Query Performance - Query/Schema/Indexes?
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USRyan
UserOwnerUserId
1. USRyan
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
2. PO
 singulars
 PostTypePostTypeId
 PTAnswer
3. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 POMySQL Query Performance - Query/Schema/Indexes?
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
2. VO
 singulars
 PostPostId
 POMySQL Query Performance - Query/Schema/Indexes?
 UserUserId
 USRyan
 VoteTypeVoteTypeId
 VTBountyStart
3. VO
 singulars
 PostPostId
 POMySQL Query Performance - Query/Schema/Indexes?
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.