Posted by frizzle on 10/13/30 11:39

Jerry Stuckle wrote:
> frizzle wrote:
> > Jerry Stuckle wrote:
> >
> >>frizzle wrote:
> >>
> >>>pjSoni wrote:
> >>>
> >>>
> >>>>Hi
> >>>>
> >>>>Try to do like when admin enters article he should enter <SPLIT> tag
> >>>
> >>>>from where you want break with article so when you read from db try to
> >>>
> >>>>split from split tag.
> >>>>
> >>>>Regards,
> >>>>Jatin
> >>>
> >>>
> >>>Thanks all for the replies.
> >>>I understand what bobzimuta and pjSoni are saying with the intro thing,
> >>>but i need two different fields, so the intro in the fulltext can
> >>>differ from just te intro.
> >>>
> >>>I think i'll go with Jerry's: SELECT COUNT(id) FROM texttable WHERE
> >>>id=1;
> >>>But the id of the fulltext would have to match the exact id of the
> >>>record with intro and other info?
> >>>Would it matter if i added LIMIT 1 to it, so the DB won't go on?
> >>>
> >>>How careful should i be asigning extra indexes? I read somewhere, that
> >>>if i asign indexes to fields that aren't going te be updated a lot,
> >>>it's not bad, otherwise it would take a lot of time ...
> >>>
> >>>And now i also have second thoughts about my field in the topic records
> >>>with the number of replies.
> >>>It goes against normalization, but i thought it would save a lot of
> >>>calculating time ...
> >>>
> >>>Frizzle.
> >>>
> >>
> >>Yes, it has to match the key of the other table. It would also the
> >>primary key of that entry. Being a primary key, it would be unique.
> >>
> >>Also, if your use INNODB tables you can make it a foreign key
> >>referencing the other table.
> >>
> >>You shouldn't need to add LIMIT 1 to the query because you have a unique
> >>index on the field. MySQL should be able to fetch everything it needs
> >>from the index (which, unlike the table, is in order).
> >>
> >>--
> >>==================
> >>Remove the "x" from my email address
> >>Jerry Stuckle
> >>JDS Computer Training Corp.
> >>jstucklex@attglobal.net
> >>==================
> >
> >
> > Why is this unlike the table in order? Since both are probably created
> > in the same instance. The Fulltext indexes have a bigger chance *not*
> > to be in order, since they could be added later ...
> >
> > And what exactly do you mean with a foreign key ?
> >
> > Frizzle.
> >
> > (Thanks a lot for your effort to help me!)
> >
>
> First of all, check out foreign keys in the MySQL documentation:
> http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-foreign-keys.html
>
> No, the table wouldn't necessarily be in order (your original one
> wouldn't either - SQL is by default unordered). But the index built on
> the primary key is always ordered (so the engine can do a quick binary
> search of the index). Therefore, MySQL can determine whether a record
> exists or not by searching the index and doesn't need to load the file
> itself in the query I showed you.
>
> But the advantage is you wouldn't have to read the full text data every
> time you need to access the summary information. You only get the full
> text when you request it.
>
> Remember - MySQL reads the entire row, even if you only ask for part of
> the data, because everything's in one file.
>
> Also, if you have a request which requires a full table scan, having the
> full text in a separate table improves the table scan (unless, of
> course, you're searching the full text data).
>
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstucklex@attglobal.net
> ==================

Ok, thanks a lot, you convinced me on this. This of course is quite
important for more other things to achieve. I will for instance not
save the number of comments as an int() in the topics table, but will
perform a count!

Frizzle.

[Back to original message]