Gossamer Forum
Home : Products : Gossamer Forum : Discussion :

Question For Jason (or for any one at GT that knows what I'm talking about)

Quote Reply
Question For Jason (or for any one at GT that knows what I'm talking about)
Hi Jason,

I'm pretty sure you know the answer to this question...

In GForum's SQL.pm

For index creation you use the following naming structure:

# b,c,d,... correspond to the following:
#
# a - post_father_id
# c - cat_id
# d - session_date
# e - user_email
# f - forum_id | from_user_id_fk
and some others.....

Is this naming convention just for internal reference? I ask because, I do have plugin for NNTP forum mirroring that (in some cases) in order to find father posts it queries forum_id_fk and post_subject (it needs to do this, since
this is Usenet Newsgroups, so in cases I don't have a valid MessageID I need to resort to matching the subject. problem is...

That particular query is showing in the slow query log, and I want to create a new index (and force my plugin to use it) i just want to know if just creating the new index (modifying SQL.pm and re-creating the tables)
won't create any strange conflicts because of my newly created index letter :) I want to call it 'y' so the new index i create will be:

# custom:
p_fy => ['forum_id_fk', 'post_subject'],


and well.. the other question is: Do I need to 'force' the use of that index? I saw in Category.pm you do have a hack to force a specific MySQL index, but I don't know if this is a special case situation.
Maybe for me just creating the new index will do? the query showing (repeteadly) in slow-query is:

SELECT * FROM Post WHERE forum_id_fk = 'FORUM FOREING KEY' AND post_subject = 'SUBJECT GOES HERE';
Quote Reply
Re: [jaltuve] Question For Jason (or for any one at GT that knows what I'm talking about) In reply to
No, the naming structure is just internal - you can name your indexes however you wish. You don't really need to add it using GT::SQL, just add it manually using MySQL command line (unless it's part of your plugin). In most cases, you shouldn't be forcing index usage, MySQL is usually smart enough to choose which indexes are best to use. Try adding the index and then doing an EXPLAIN, it should tell you if it's using your index. It looks like a simple enough query and I'm pretty sure it will use your index.

Adrian
Quote Reply
Re: [brewt] Question For Jason (or for any one at GT that knows what I'm talking about) In reply to
Thanks Adrian,

Adding the new index definitely helped a lot!! ;)

I had, one more query bothering me.. I have a global to obtain a given user latest posts. My global results in the following query:

mysql> EXPLAIN SELECT * FROM Post WHERE (post_root_id = '0' AND post_moved IS NULL AND post_deleted = '0' AND user_id_fk = '4' AND forum_id_fk IN (1,2,3)) ORDER BY post_time DESC LIMIT 5;

+----+-------------+-------+------+-------------------------------+-------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-------------------------------+-------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | Post | ref | p_fmn,p_fn,p_rfl,p_rfmp,p_ufm | p_rfl | 4 | const | 13 | Using where; Using filesort |
+----+-------------+-------+------+-------------------------------+-------+---------+-------+------+-----------------------------+

Notice, the index being used: p_rfl it includes:

post_root_id
forum_id_fk
post_latest_reply

Would creating a new index (p_mynewindex), with:

forum_id_fk
post_root_id
post_time
user_id_fk

make sense for this query?? what about post_moved and post_deleted? I guess they won't be necessary right?
Quote Reply
Re: [jaltuve] Question For Jason (or for any one at GT that knows what I'm talking about) In reply to
Well, of course it's best if you have all columns used in the query in the index, but you have to find the balance because having all the columns in the index just creates a bigger index, which has its downsides. You might want to try dropping post_time, or adding user_id_fk to the p_rfl index.

Adrian
Quote Reply
Re: [brewt] Question For Jason (or for any one at GT that knows what I'm talking about) In reply to
Hi Adrian,

I'll test some combinations later to see what works best.

Thank you for your comments/suggestions. Much appreciated.

Take care,