Mysql adding index for column type text does not improve performance while using select query

74
March 15, 2019, at 7:40 PM

I am using InnoDB. From this question, I found out that I have to specify the length if I want to add index to columns which type is TEXT.

But after successfully adding index, the performance for the select query stay the same. Anyone know why? I did check the index with show index from tableName and the index did exist.

So it was the last two table EventResultsFinalSummary and 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;

I added index to the column that I joined. EventID, CoupleId, Competition_id for EventResultsFinalSummary and EventID, EventRound, Competition_id for EventResultsPrelims by using query like following.

My question is that when those columns have type like varchar or int, the select * query will only take 1s. But it take 26s when the type is Text.

ALTER TABLE `EventResultsPrelims` ADD INDEX(`EventID`(6));
READ ALSO
How to find cpanel credential? [on hold]

How to find cpanel credential? [on hold]

I am facing a mail server Debian 7Few years ago, roundcube 0

44
How to create mysql container with initial data in kubernetes?

How to create mysql container with initial data in kubernetes?

I want to set initial data(script file which creates database and table) on MySQL of containerI have another pod which will talk with mysql pod and inserts data in the table

61
Tableview not showing new added data, only shows after restarting program (javafx)

Tableview not showing new added data, only shows after restarting program (javafx)

The tableview only shows new added data when I restart the programPlease help !

79