Posted by Jerry Stuckle on 02/07/06 16:54

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
==================

[Back to original message]