Return bool from query

[frizzle]

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_arr ay() 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.

[Steve]

On Tue, 07 Feb 2006 14:42:57 -0800, 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_arr ay() to decide wether or not to
>>>>>show 'read_more', or should i create an extra boolean field in the DB
>>>>>saying fulltext y/n ?
>>>>>
>>>>>Greeting s 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.
>>>>js*******@a ttglobal.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.
>>js*******@att global.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_avail able' 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.
js*******@attgl obal.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.

Always normalise your data model first. Then, if it isn't fast enough, add
in shortcuts. Databases ( even mysql (: ) are really powerful - but that
also means that you can really screw everything up if you don't stand back
first, and get a proper idea of what you're trying to do first.

There must be a copy of C.J.Date on the internet somewhere - it must be 30
years old by now.

Steve

[pjSoni]

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

[frizzle]

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.

[Jerry Stuckle]

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.
js*******@attgl obal.net
=============== ===

[frizzle]

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.
js*******@attgl obal.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!)

[Jerry Stuckle]

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
calculatin g 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.
js*******@att global.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/...eign-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.
js*******@attgl obal.net
=============== ===

[frizzle]

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
calculatin g 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.
js*******@att global.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/...eign-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.
js*******@attgl obal.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.

[frizzle]

I am btw not using INNODB, but ISAM.
(Neither ring a bell for me, but that's what the docs said.)

Frizzle.

[Jerry Stuckle]

frizzle wrote:

I am btw not using INNODB, but ISAM.
(Neither ring a bell for me, but that's what the docs said.)

Frizzle.

If you're going to use foreign keys, you need to change to innodb. It's
not hard - just call ALTER TABLE on each one.

Or use PHPMyAdmin - a great tool if you're not familiar with it.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attgl obal.net
=============== ===

[frizzle]

Jerry Stuckle wrote:

frizzle wrote:

I am btw not using INNODB, but ISAM.
(Neither ring a bell for me, but that's what the docs said.)

Frizzle.

If you're going to use foreign keys, you need to change to innodb. It's
not hard - just call ALTER TABLE on each one.

Or use PHPMyAdmin - a great tool if you're not familiar with it.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attgl obal.net
=============== ===

I actually use PHPmyAdmin, but don't know where to set it to INNODB, or
my host disabled that, (i can choose form 3 other ones though ...)

Frizzle.