Simon Slavin wrote...
On 29 Jan 2014, at 10:42pm, jose isaias cabrera <[email protected]>
wrote:
The tables are created this way:
CREATE TABLE OpenProjects (id integer primary key, ProjID integer,
createDnT, unique(id));
CREATE TABLE OpenJobs (id integer primary key, ProjID integer, Task,
unique(id));
Thank you for this information which saved many annoying questions. To
start off, I agree with what Roman said: using multiple tables for the
same column layout is almost always a bad idea.
I know I can do,
select * from OpenJobs where ProjID = 2;
and get all the OpenJobs ids that belong to project id 2. But, this is
taking a long time.
Have you created a useful index ? Try doing
CREATE INDEX OjPid ON OpenJobs (ProjID)
Holey moley! Thanks.
then try your SELECT again.
knocked off 2 minutes now returns all the tasks in less than a second.
Thanks. I am going to add some more to the dates. As also there are dates
searches and queries that also take a long time. This should fix the
problem.
By the way, once you have this working I would suggest (from my
experience) that you change your database design a little. Instead of
having a table containing just your open jobs, have a table containing all
jobs, and add a "status" column which can contain 'open', 'paid', and
perhaps even other statuses like 'awaiting payment'.
That's in another table. :-( I know. This is a really bad design, but I
would slowly start making this changes.
Thanks so much!
PS: Read the other response about the whole architectural design...
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
