Posted by frizzle on 02/08/06 00:42

Jerry Stuckle wrote:
> frizzle wrote:
> > Jerry Stuckle wrote:
> >
> >>frizzle wrote:
> >>
> >>>Jerry Stuckle wrote:
> >>>
> >>>
> >>>>frizzle wrote:
> >>>>
> >>>>
> >>>>>Hi there,
> >>>>>
> >>>>>I have a mySQL system with a news publishing part in it:
> >>>>>Admins can create new items with text in it, and they have an option to
> >>>>>create 'fulltexts', so you'd get "read more ..." on the front page,
> >>>>>click it and read the fulltext.
> >>>>>
> >>>>>Is there a possibility for mySQL (query) to check if 'fulltext' is
> >>>>>empty or not, and only return true or false, so i don't have to put the
> >>>>>whole fulltext into the mysql_fetch_array() to decide wether or not to
> >>>>>show 'read_more', or should i create an extra boolean field in the DB
> >>>>>saying fulltext y/n ?
> >>>>>
> >>>>>Greetings Frizzle.
> >>>>>
> >>>>
> >>>>Put it in a separate table with just the article's id and the text. If
> >>>>the id exists in the second table, there is more text.
> >>>>
> >>>>--
> >>>>==================
> >>>>Remove the "x" from my email address
> >>>>Jerry Stuckle
> >>>>JDS Computer Training Corp.
> >>>>jstucklex@attglobal.net
> >>>>==================
> >>>
> >>>
> >>>This would have me quering against an extra table. Would'nt it be
> >>>quicker to add an extra boolean field then?
> >>>
> >>>Frizzle.
> >>>
> >>
> >>Why? Joins are quick. Probably a lot faster than unnecessarily
> >>processing empty text fields in the first table.
> >>
> >>--
> >>==================
> >>Remove the "x" from my email address
> >>Jerry Stuckle
> >>JDS Computer Training Corp.
> >>jstucklex@attglobal.net
> >>==================
> >
> >
> > I understand what you're saying. I think i'm underestimating the speed
> > of Joins (mySQL)
> > Not to be a nag, but would it still be faster to add an extra field to
> > the original table, 'fulltext_available' tinyint(1), which tells me to
> > look for it or not?
> >
> > And, am i wrong believing that if i use 'SELECT fieldname1, name2 etc.'
> > in the query, it leaves the unmentioned fields unbothered, thus not
> > being influenced by their size/contents ?
> >
> > Frizzle.
> >
>
> Yes and no. It depends on the implementation of the database.
>
> In MySQL, everything in a table is kept in one file. So if you have
> your full text in that table, a table scan will read everything in the
> table and throw away the columns you don't want. If you have a lot of
> text in relation to the rest of the row data, this can be significant
> overhead. If the full text is in a separate table, it will only be read
> when you request information from that table.
>
> Additionally, is the absolute speed necessary? Are you talking dozens
> (or even hundreds) of queries per second? I doubt it.
>
> Keeping large amounts of data you only occasionally access is generally
> a better way of doing things. Putting a T/F column in the table just
> makes for another column you have to manage (and violates normalization
> principles).
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstucklex@attglobal.net
> ==================

I don't know (yet) how much queries there will be, but i guess not
*that* much.
My lack of english causes me te seize your intention in the last part.
Do you mean i should put the fulltexts in another extra table?
I btw hardly doubt if it will be neccesary, since those texts won't be
that big either.
(For mySQL / PHP then ... )
Again, it's a news system, there won't be a whole version of The Da
Vinci Code behind 'read more'.

Frizzle.

[Back to original message]