MySQL Forums
Forum List  »  Newbie

Can this be made faster?
Posted by: Doug Matthews
Date: May 09, 2020 06:01PM

Hi. I made an app to keep track of where I am in web comics, so I don't have to remember what chapters I've read and which I did not. The structure is pretty simple:

TABLE: manga
FIELDS: id (int, key, autoinc), title, url

TABLE: chapters
FIELDS: cid (int, key, autoinc), id, chapter, url

TABLE: users
FIELDS: uid (int, key, autoinc), userid, passwd

TABLE: readlog
FIELDS: rlid (int, key, autoinc), uid, cid, dateread

When I pull up the page, I want it to dump a list of titles that have unread chapters. The SQL I use for that is:

SELECT m.id,title,m.url,
(SELECT COUNT(c.cid) FROM chapters c LEFT OUTER JOIN readlog r ON c.cid = r.cid AND r.uid = ? WHERE c.id = m.id AND dateread IS NULL ORDER BY chapter+0) AS chapters 
FROM manga m ORDER BY title

It works, but even with only 104 manga and 5451 chapters it takes almost three seconds to load the page. I improved it from before, where I was just selecting from manga then when looping that select chapters, that took about eight seconds.

Even three seconds though feels like eternity when waiting for a page to load. Is there a way for me to make it faster? I read about indexing and how it can speed up queries, but I don't know how to do that or what field I would index (assuming that would fix it).

Any advice would be appreciated, thanks a lot!

Options: ReplyQuote


Subject
Written By
Posted
Can this be made faster?
May 09, 2020 06:01PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.