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!