问题描述:

I have a website that relies on data base queries pretty heavily when custom results are requested. Theses queries inner join about 6 different tables for certain data, the data from those tables remain static for long. I have been looking at making a javascript array and using method 1 to look the data up instead to remove some of those inner joins, not all of them, but just enough to make sure my database isn't overrun. I have an example query.

//search function

function FindData(WantedID) {

var result = Array.filter(function( obj ) {

return obj.ID== WantedID;

});

return result[0];

};

//query

SELECT ItemInfo1.SimpleDescription AS Item1Simple,

ItemInfo1.FullDescription AS Item1Description,

ItemInfo1.Name AS Item1Name,

ItemInfo1.GoldCost AS Item1GoldCost,

ItemInfo2.SimpleDescription AS Item2Simple,

ItemInfo2.FullDescription AS Item2Description,

ItemInfo2.Name AS Item2Name,

ItemInfo2.GoldCost AS Item2GoldCost,

ItemInfo3.SimpleDescription AS Item3Simple,

ItemInfo3.FullDescription AS Item3Description,

ItemInfo3.Name AS Item3Name,

ItemInfo3.GoldCost AS Item3GoldCost,

ItemInfo4.SimpleDescription AS Item4Simple,

ItemInfo4.FullDescription AS Item4Description,

ItemInfo4.Name AS Item4Name,

ItemInfo4.GoldCost AS Item4GoldCost,

ItemInfo5.SimpleDescription AS Item5Simple,

ItemInfo5.FullDescription AS Item5Description,

ItemInfo5.Name AS Item5Name,

ItemInfo5.GoldCost AS Item5GoldCost,

ItemInfo6.SimpleDescription AS Item6Simple,

ItemInfo6.FullDescription AS Item6Description,

ItemInfo6.Name AS Item6Name,

ItemInfo6.GoldCost AS Item6GoldCost,

ItemInfo7.SimpleDescription AS Item7Simple,

ItemInfo7.FullDescription AS Item7Description,

ItemInfo7.Name AS Item7Name,

ItemInfo7.GoldCost AS Item7GoldCost,

Spell11.Description AS Spell1Description,

Spell11.Name AS Spell1Name,

Spell11.SpellKey AS Spell1Key,

Spell11.Cooldown AS Spell1Cooldown,

Spell12.Description AS Spell2Description,

Spell12.Name AS Spell2Name,

Spell12.SpellKey AS Spell2Key,

Spell12.Cooldown AS Spell2Cooldown,

masteries.MasteryID,

masteries.MasteryName,

masteries.MasteryDescription,

matchhistory.*,

championdb.ChampName,

player.Alias,

player.RoleSlug,

player.Region,

player.AltRegion,

player.playerid,

player.SummonerIcon,

teams.Name,

teams.ID AS TeamID,

player.League,

player.Division,

player.Points,

player.isFreshBlood,

player.isHotStreak,

player.isVeteran,

player.Wins,

player.Losses,

player.FirstChamp,

player.SecondChamp,

player.ThirdChamp

FROM matchhistory

INNER JOIN player ON matchhistory.SummonerID = player.playerid

INNER JOIN teams ON player.Team = teams.ID

INNER JOIN summonerspells AS Spell11

ON matchhistory.Spell1 = Spell11.SpellID

INNER JOIN summonerspells AS Spell12

ON matchhistory.Spell2 = Spell12.SpellID

INNER JOIN items AS ItemInfo1 ON matchhistory.Item0 = ItemInfo1.ItemID

INNER JOIN items AS ItemInfo2 ON matchhistory.Item1 = ItemInfo2.ItemID

INNER JOIN items AS ItemInfo3 ON matchhistory.Item2 = ItemInfo3.ItemID

INNER JOIN items AS ItemInfo4 ON matchhistory.Item3 = ItemInfo4.ItemID

INNER JOIN items AS ItemInfo5 ON matchhistory.Item4 = ItemInfo5.ItemID

INNER JOIN items AS ItemInfo6 ON matchhistory.Item5 = ItemInfo6.ItemID

INNER JOIN items AS ItemInfo7 ON matchhistory.Item6 = ItemInfo7.ItemID

INNER JOIN masteries ON matchhistory.KeystoneID = masteries.MasteryID

INNER JOIN championdb ON matchhistory.ChampID = championdb.ChampID

ORDER BY matchhistory.TimeStamp DESC LIMIT 10;

I'm looking to reduce the item inner joins as their are 6 items in the main table, and i need to inner join each time to get the specific data for each item. In Total there is 200 items. I am using mysql, I am also looking at replacing inner join with WHERE ID IN to increase query speed. I can cache the array results also to make it faster, the array data is made with a once off query. My database server is separate from my webapp server

网友答案:

You are doing several mistakes

1) you fetch whole DB table join into a javascript and than you try to find specific match. Thats very bad idea performance wise, especialy if you expect your tables to grow. Where clausule is there for a reason.

2) your DB tables are not properly indexed it seems. based on your explain mysql is doing full table scan on player table and than scaning match history table adding multiple rows from other tables, creating temorary table for sorting (might be eaven Disk Table), sorting it (wihout index) and than fetching your 10 lines of data.

What you need is to add index on matchtable on ID colum you should be matching and than add timestamp colum to enable sorting by index. this have to be combined with where clausule containing specific ID your are searching for.

But .... based on sections of code you provided I am not sure that your problem is with database, Because eaven small instance of mysql have to handle this in few ms.

相关阅读:
Top