时间戳

首页 > 解决方案 > Mysql在使用选择查询时为列类型文本添加索引不会提高性能

mysql - Mysql在使用选择查询时为列类型文本添加索引不会提高性能

问题描述

我正在使用 InnoDB。从这个问题中，我发现如果我想为类型为 TEXT 的列添加索引，我必须指定长度。

但是成功添加索引后，选择查询的性能保持不变。有谁知道为什么？我确实使用 tableName 中的 show index 检查了索引，并且该索引确实存在。

所以它是最后两个表 EventResultsFinalSummary 和 EventResultsPrelims。

CREATE OR REPLACE VIEW ScheduleView AS
SELECT s.ScheduleID, e.EventRound, e.EventRoundsID, e.EventID, e.NumberCouplesInRound, n.NumberOnBack, eic.EventName AS 'Division',
       CONCAT(a1.FirstName, ' ', a1.LastName, ' - ', a2.FirstName, ' ', a2.LastName) AS 'Couple',
       s.SessionID AS 'Session', erfs.CouplePlace, c.CoupleID,
       s.Timeslot, s.SubFloor ,s.itemDuration,s.HeatNumber, o.ActivityName, st.StudioName AS 'DanceStudio', a.AgeName AS 'Age', s.competition_id, erp.CoupleVotes
FROM Schedule AS s
    LEFT JOIN EventRounds AS e ON s.EventRoundID = e.EventRoundsID AND s.competition_id = e.competition_id
    LEFT JOIN OtherActivities AS o ON s.OtherActivitiesID = o.OtherActivitiesID AND s.competition_id = o.competition_id
    LEFT JOIN EntriesEvents AS ee ON e.EventID = ee.EventID AND e.EventRound <= ee.EventRound AND e.Competition_id = ee.Competition_id
    LEFT JOIN Couples AS c ON ee.EntryID = c.CoupleID AND ee.Competition_id = c.Competition_id
    LEFT JOIN NumSysComps AS n ON c.CompetitorIDMan = n.CompetitorIDMan AND c.Competition_id = n.Competition_id
    LEFT JOIN Attendees AS a1 ON c.CompetitorIDMan = a1.AttendeeID AND c.Competition_id = a1.Competition_id
    LEFT JOIN Attendees AS a2 ON c.CompetitorIDLady = a2.AttendeeID AND c.Competition_id = a2.Competition_id
    LEFT JOIN Studios AS st ON a1.StudioID = st.StudioID AND a1.Competition_id = st.Competition_id
    LEFT JOIN EventsInComp AS eic ON eic.EventID = e.EventID AND eic.Competition_id = e.Competition_id
    LEFT JOIN ProAmSingleDanceEvents AS psd ON eic.ProAmSingleDanceEventID = psd.ProAmSingleDanceEventID AND eic.Competition_id = psd.Competition_id
    LEFT JOIN ProAmMultiDanceEvents AS pmd ON eic.ProAmMultiDanceEventID = pmd.ProAmMultiDanceEventID AND eic.Competition_id = pmd.Competition_id
    LEFT JOIN Ages AS a ON (
        psd.AgeID = a.AgeID AND psd.Competition_id = a.Competition_id
        OR
        pmd.AgeID = a.AgeID AND pmd.Competition_id = a.Competition_id
    )
    LEFT JOIN EventResultsFinalSummary AS erfs ON e.EventID = erfs.EventID AND c.CoupleID = erfs.CoupleID AND s.Competition_id = erfs.Competition_id
    LEFT JOIN EventResultsPrelims AS erp ON e.EventID = erp.EventID AND erp.EventRound = e.EventRound AND c.CoupleID = erp.CoupleID AND s.Competition_id = erp.Competition_id
    ORDER BY s.ScheduleID;

我在加入的列中添加了索引。EventResultsFinalSummary 的 EventID, CoupleId, Competition_id 和 EventResultsPrelims 的 EventID, EventRound, Competition_id 使用如下查询。

我的问题是，当这些列的类型为 varchar 或 int 时，select * 查询只需要 1 秒。但类型为文本时需要 26 秒。

ALTER TABLE `EventResultsPrelims` ADD INDEX(`EventID`(6));

标签： mysqlindexing

解决方案

推荐阅读