optimize needed, the 2nd time

[Xuefer]

already discuzzed in [webmasterworld.com...]
still not solved yet

i read "optimize" in mysql manual again and again
but still see nothing i can do with that case

table `post`
postid INT
fourmid INT
ctime INT
mtime INT
title VARCHAR
posttext TEXT
INDEX postid
INDEX forumid

SELECT * FROM post WHERE forumid=1

but for a forumid=1, there's many and many rows INSERT in `post` every day
doing a explain shows me:
string(8) key=forumid
string(5) ref=const
string(4) rows=125130 (sooo many rows)

forumid=1
any special scheme?

[coopster]

I'm assuming this isn't your actual query because this should run lightning fast. The original post refers to a multiple LEFT JOIN solution. Can you confirm?

[Xuefer]

true
so? in my origin reply, i asked:
is it faster to do a simple query to get IDs
and do a complex left joins **** where id in($IDs)? why?

btw:
i sometimes do
FROM post LEFT JOIN thread LEFT JOIN forum LEFT JOIN user..
but most time do:
FROM thread LEFT JOIN post LEFT JOIN forum LEFT JOIN user..

[Xuefer]

and of cos i can do:
LIMIT 0, 10

but..
someone may request on $page=100:
so query:
LIMIT 1000, 10

[coopster]

Can you post

1. your query statement in full?
2. As well as the primary keys
3. and indexes on your joined files?
4. Lastly, it would help if you ran an EXPLAIN and posted those results as well.

I realize it's a big ask, but I don't know how else we might help troubleshoot.

[Xuefer]

here we go:
SELECT ....
FROM post USE INDEX(postdate) LEFT JOIN thread ON post.postid=thread.firstpost
LEFT JOIN user ON user.userid=post.posterid
LEFT JOIN forum ON forum.forumid=post.forumid
WHERE post.postdate>1075000192 AND post.posterid=129272 AND post.siteid=2
ORDER BY post.postdate DESC
LIMIT 10,11

table post
type range
possible_keys postdate
key postdate
key_len 4
ref null
rows 509670
Extra Using where

table thread
type ref
possible_keys firstpost
key firstpost
key_len 4
ref post.postid
rows 1
Extra

table user
type eq_ref
possible_keys PRIMARY
key PRIMARY
key_len 4
ref post.posterid
rows 1
Extra

table forum
type eq_ref
possible_keys PRIMARY
key PRIMARY
key_len 2
ref post.forumid
rows 1
Extra

[Xuefer]

mysql manual said:
It's not normally useful to split a table into different tables just because the rows get ``big.'' To access a row, the biggest performance hit is the disk seek to find the first byte of the row. After finding the data, most modern disks can read the whole row fast enough for most applications. The only cases where it really matters to split up a table is if it's a MyISAM table with dynamic record format (see above) that you can change to a fixed record size, or if you very often need to scan the table and don't need most of the columns.

"It's not normally useful to.."
means there's some exception, right?
for a forum/bbs, there's many place need to list titles, not content, is this a exception case?
seems only reading the thread/post need content
/
/home.htm
/category88.htm
/forum21/
all these pages don't require content

should it be split into another table to make "post" table smaller?

[Xuefer]

summary of this topic:
even after doing "where forumid=1" there's too much rows

$sql_1 =
"select .. from post
where forumid=1
order by postdate desc
limit 0,11";

because count(DISTINCT forumid) is not changed, in another word: limited-constant
but count(*) is endless
avengly rows of forumid=xx
equals: endless/count(DISTINCT forumid)
equals: endless/limited-constant
equals: endless

as u may think, it's nothing different from doing:
$sql_2 = "select * from table order by postdate limit $offset,$count";

two way to go:
1. add another condition in where (possible? how)
2. find possible way if we can optimized $sql_2
the problem becomes:
[webmasterworld.com...]