Re: [sqlite] Query - finding objects with given properties

[inZania]

Rich,

You're certainly right.  However I've actually simplified the table
structure here in order to not over-complicate matters.  I'm pretty certain
the schema is good, but if you have the time/willingness to work through the
whole thing with me, my AIM is NeonNinjaSan or ICQ 51549640 - I don't want
to waste your time if you don't have it though.  Cheers.

Also, to answer your question, yes an object with the property "green" might
also have the property "yellow" with a different score.


Rich Shepard wrote:
> 
>Why have the ID columns? If each Object_name and Property_name is
> unique,
> they don't need a surrogate/artificial integer primary key.
> 
>I'm not trying to sound pompous, but your schema may be causing your
> difficulties in writing quick running queries.
> 
> 
>Can an object also have the properties of "yellow," "cool," and "dull?"
> 
>If you are concerned with query running times it's often good to look
> at
> the DDL and see if it's as efficient as can be.
> 
> Rich
> 
> -- 
> Richard B. Shepard, Ph.D.   |  Integrity   
> Credibility
> Applied Ecosystem Services, Inc.|Innovation
>  Voice: 503-667-4517  Fax:
> 503-667-8863
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Query---finding-objects-with-given-properties-tp22312313p22313518.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query - finding objects with given properties

[inZania]

Igor,

Thanks for the help!  However, I'm running into an error with the
sub-queries returning more than 1 result.  I suppose it has to do with the
fact that the property "hot" may have more than 1 entry (one for each
object).  Thanks!


Igor Tandetnik wrote:
> 
> select * from objects where
> object_id in (select object_id from properties where 
> property_name='green' and score > 3) and
> object_id in (select object_id from properties where property_name='hot' 
> and score < 2) and
> object_id in (select object_id from properties where 
> property_name='bright' and score > 0);
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Query---finding-objects-with-given-properties-tp22312313p22313457.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Query - finding objects with given properties

[inZania]

I am attempting to write a query that runs as fast as humanly possible... to
be honest, I'm having a really hard time even getting started with it.  I'm
generally decent with SQL but for some reason this one is eluding me in
terms of doing it within a single query, which would help my application's
speed.

The situation is this: there are two tables, one called "objects" (fields:
object_id[int] and object_name) and properties (fields: object_id[int] and
property_name and score[int]).  Objects have one or more properties.

The objective of the query is to return an object who has all of the given
properties scored above or below the given value.

For example, I might look for the object_id of an object who has the
property "green" scored above 3, the property "hot" scored below 2, and the
property "bright" scored above 0.  It might have other properties, too, but
it must have these 3.

And then, for the second part, I need a query that will select properties
which are NOT in this set of given properties but are relevant to the
returned set of objects.  In this part, I'm not concerned with the
property's score - just that it is relevant to the objects.  In other words,
I might now find that the property "hard" is relevant to 3/4 objects (even
though one of them had a very low score for the hard property, another had a
very high one, etc).  Basically I want to know which property, once I can
determine the score, will help narrow down the object-set the most, so that
I can collect data for the most relevant property to narrow down the object
set.

Thanks so much for your help!
-- 
View this message in context: 
http://www.nabble.com/Query---finding-objects-with-given-properties-tp22312313p22312313.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Query Optimization Help

[inZania]

Hello,

I have a query that is slowing down my application significantly; in some
cases, it takes 20+ seconds (this is in a SQLite database in an iPhone app,
which is why it is so slow - the iPhone doesn't have as much system
resources).  If anybody could help me optimize this query, I'd appreciate it
very much.

The situation is this: there is a table, "cards", which I am searching. 
Each card has a card_id, name, text, etc.  There is also a table "card_tags"
which has only the rows "card_id" and "tag", because a single card may have
several tags.  The query I'm trying to execute is attempting to search the
card's name, text, OR any of its tags for a specific search string.

Here's the query I've constructed that is operating slowly:
SELECT DISTINCT cards.* FROM cards LEFT JOIN card_tags ON
cards.card_id=card_tags.card_id WHERE (cards.name LIKE '%query%' OR
cards.text LIKE '%query%' OR card_tags.tag LIKE '%query%')

Any help would be appreciated!
-- 
View this message in context: 
http://www.nabble.com/Query-Optimization-Help-tp21958799p21958799.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users