我剛啟動我的網(wǎng)站，5 天后它變慢了，加載網(wǎng)站花了將近 30 秒，因?yàn)?mysql 數(shù)據(jù)庫變得更大，加上 50-60k 行。我已經(jīng)搜索了幾個小時的解決方案，但我沒有找到任何可以實(shí)現(xiàn)來優(yōu)化我的查詢的東西。$idstack = $_SESSION['ids']; $stmt = $mysqli->prepare("SELECT maps.id, maps.name, maps.date, maps.mcversion, maps.mapid, maps.description, maps.java, maps.bedrock, maps.schematic, users.username, users.rank, users.verified, (SELECT COUNT(*) FROM likes WHERE likes.mapid = maps.id) AS likes, (SELECT COUNT(*) FROM downloads WHERE downloads.mapid = maps.id) AS downloads, (SELECT COUNT(*) FROM views WHERE views.mapid = maps.id) AS views FROM maps INNER JOIN users ON maps.userid = users.id WHERE maps.id NOT IN ( '" . implode( "', '" , (array)$idstack ) . "' ) ORDER BY RAND() DESC LIMIT 15"); $stmt->execute(); $result = $stmt->get_result();這個查詢返回我想要的，但是當(dāng)數(shù)據(jù)庫有數(shù)千行時它很慢。知道如何優(yōu)化它嗎？
1 回答
慕尼黑8549860
TA貢獻(xiàn)1818條經(jīng)驗(yàn) 獲得超11個贊
對于您的查詢，您需要索引：
likes(mapid)downloads(mapid)views(mapid)maps(id, userid)users(id)
如果將列聲明為主鍵，您可能已經(jīng)擁有其中一些索引。
也就是說，查詢可能仍然難以優(yōu)化。 NOT INwithORDER BY會很棘手。
這些創(chuàng)建為：
create index idx_likes_mapid on likes(mapid);
- 1 回答
- 0 關(guān)注
- 131 瀏覽
添加回答
舉報
0/150
提交
取消