Small Performance improvement with MySQL (1 Viewer)

[pgebauer]

i made some additional indexes in the MySQL Database and got a small performance boost.
Here are the SQL-Statements. Maybe these Statements can be included in future releases.

ALTER TABLE `setting` ADD INDEX ( `tag` ) ;
ALTER TABLE `tuningdetail` ADD INDEX `IDX_TuningDetails2` ( `networkId` , `transportId` , `serviceId` );
ALTER TABLE `channel` ADD INDEX ( `isRadio` ) ;
ALTER TABLE `channel` ADD INDEX ( `isTv` ) ;
ALTER TABLE `channel` ADD INDEX ( `sortOrder` ) ;
ALTER TABLE `radiogroupmap` ADD INDEX ( `SortOrder` ) ;

 

[rtv]

I don't see where a query would use IDX_TuningDetails2 but other than that your findings are good - especially the settings one...

 

[pgebauer]

i have logged all queries that do not use indexes.
an example query for using the index is:

select idTuning, idChannel, name, provider, channelType, channelNumber, frequency, countryId, isRadio, isTv, networkId, transportId, serviceId, pmtPid, freeToAir, modulation, polarisation, symbolrate, diseqc, switchingFrequency, bandwidth, majorChannel, minorChannel, pcrPid, videoSource, tuningSource, videoPid, audioPid, band, satIndex, innerFecRate, pilot, rollOff, url, bitrate from TuningDetail where networkId = 1 and transportId = 1080 and serviceId = 8801;

I don't really know when the query happens, but it happens.

 

[rtv]

i have logged all queries that do not use indexes.
an example query for using the index is:

select idTuning, idChannel, name, provider, channelType, channelNumber, frequency, countryId, isRadio, isTv, networkId, transportId, serviceId, pmtPid, freeToAir, modulation, polarisation, symbolrate, diseqc, switchingFrequency, bandwidth, majorChannel, minorChannel, pcrPid, videoSource, tuningSource, videoPid, audioPid, band, satIndex, innerFecRate, pilot, rollOff, url, bitrate from TuningDetail where networkId = 1 and transportId = 1080 and serviceId = 8801;

I don't really know when the query happens, but it happens.

Yes - you're right; the DVB-EPG-Code does lookup the channel this way. As I don't use it I haven't seen those queries before

 

[steve walsh]

How to apply these "tweaks"...

Hello,

I'm no database expert (this will soon become obvious!) so feel free to point out where I'm going wrong....
But for other non-experts I thought I'd share my experiences...

So, guessing at what to do, I figured I had to type them into the MySQL command line client.. (this is in the Start/All Programs/MySQL folder)
On first attempt I got the message : no database selected
It didn't take long to figure out how to solve this (about 30 secs)....
"help;" or "\h" pointed me towards the "connect" command : connect mptvdb
Then when I ran the SQL-statements everything seemed to work - I got no errors.
I typed quit afterwards

I hope this helps others with little/no db experience...

 

[revs]

The SQL Server equivalent is as follows (I think)

this: ALTER TABLE `setting` ADD INDEX ( `tag` ) ;
becomes: CREATE INDEX [tag] ON [dbo].[setting] ( [tag] ) ;