|
|
Posted by Jerry Stuckle on 10/12/42 11:39
frizzle wrote:
> 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.
>
I should also add - something like
SELECT COUNT(id) FROM texttable WHERE id=1;
is quite quick. Chances are since id is a primary key, MySQL won't even
go to the table itself - it will just see if it exists in the index.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
Navigation:
[Reply to this message]
|