(2010-11-21, 12:13 PM)MattRogowski Wrote: [ -> ]What, so none of your tables have any indexes on them?? Just gone through the tables in my forum, the only ones with no indexes are the adminsessions and calendarpermissions. Can you give an example of what isn't indexed on your forum that you think should be...??
i've already added the indices myself, so i'm not sure if it was me that added them, or there were already there...although for the most part, there were no other columns that were indexed beyond the primary key.
forums: pid, active, disporder
adminsessions: dateline
announcements: fid, uid, startdate, enddate
attachments: pid,uid,visible,posthash
usertitles: posts
users: suspensiontime, moderationtime
thread subscriptions: uid, tid
there's more, but yea, these guys aren't indexed. basically, any column used in where/order by/join clauses should indexed.
also: modtools isn't a normalized table.
the column "modtools.forums" is a list of ids inside a text column. it should be split up into a 2 tables...because right now, querying it is done using the "LIKE" comparer with a wildcard on both the sides of the target (i.e. %forum_id%) which is really inefficient, and no index will be used.
just saw this in my logs: (i've obfuscated some parts of it)
SELECT p.pid
FROM mybb_posts p
WHERE p.ipaddress = '--USER IP ADDRESS---'
AND p.fid = '30'
AND p.subject = '--- MESSAGE SUBJECT ----'
AND p.message = '--- MESSAGE CONTENT ---'
AND p.posthash = '076089c0aa18ad76213666d144208699'
this query is pretty much guaranteed to not use an index, and on a table as big as mybb_posts, that's definitely gonna cause problems for some people. it took 8 seconds to execute this query
can anyone give me a confirmation that those columns aren't indeed indexed? I'm not sure whether my installation is incredibly screwed up, or perhaps is some other reason