mysql query

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

mysql query

David Singleton-4
Hey list, having some problems.  I have this search and page load I have
that is taking over 2 minutes to load.  When looking over the code the
biggest issue here seems to be redundancy.  I've fixed quite a few
issues and now the biggest obstacle I have is my inexperience with mysql
query writing.

So, here is the set up. I have two tables: pdf_Errors and
pdf_PageHistory.   The error tables has a list of errors that each page
had when it came to us(we're a printing company) the PageHistory table
has a record saying when the page came in, what color it was, ext.  The
pdf_Errors table may have many records, or no records. The
pdf_PageHistory table may have 1 record or no records for any given
page.

When we first load up a job, I have a list of pages. (1, 3, 4, 5, 6,
ext.) These pages are put into a "part", part is also tracked in the
tables.

Here is where I'm hitting my problem.  Its set up so as soon as the page
loads, they can view if the part has any errors, and if it already has a
history filled out for it.  Since these are in two different tables, I'm
having to query each one of them individually, (and once per page.)

What I want, and can't seem to get (if its even possible) is something
like this.

Page      HasPDf_Error     HasPDF_Hist
22           true                       false
23           false                      false
29           true                       true

I'd played around with mysql using something like IF(select count(*)
from pdf_Errors where PageNo = '22' > 0, "true", "false") as
HasPDF_Error,
But I'm screwing something up somewhere.  Any advice?


--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

Reply | Threaded
Open this post in threaded view
|

Re: mysql query

Steve Upton
At 3:16 PM -0500 4/10/08, David Singleton wrote:

>Hey list, having some problems.  I have this search and page load I have
>that is taking over 2 minutes to load.  When looking over the code the
>biggest issue here seems to be redundancy.  I've fixed quite a few
>issues and now the biggest obstacle I have is my inexperience with mysql
>query writing.
>
>So, here is the set up. I have two tables: pdf_Errors and
>pdf_PageHistory.   The error tables has a list of errors that each page
>had when it came to us(we're a printing company) the PageHistory table
>has a record saying when the page came in, what color it was, ext.  The
>pdf_Errors table may have many records, or no records. The
>pdf_PageHistory table may have 1 record or no records for any given
>page.
>
>When we first load up a job, I have a list of pages. (1, 3, 4, 5, 6,
>ext.) These pages are put into a "part", part is also tracked in the
>tables.
>
>Here is where I'm hitting my problem.  Its set up so as soon as the page
>loads, they can view if the part has any errors, and if it already has a
>history filled out for it.  Since these are in two different tables, I'm
>having to query each one of them individually, (and once per page.)
>
>What I want, and can't seem to get (if its even possible) is something
>like this.
>
>Page      HasPDf_Error     HasPDF_Hist
>22           true                       false
>23           false                      false
>29           true                       true
>
>I'd played around with mysql using something like IF(select count(*)
>from pdf_Errors where PageNo = '22' > 0, "true", "false") as
>HasPDF_Error,
>But I'm screwing something up somewhere.  Any advice?

are you using joins at all? They will give you much higher performance. That and indexing the columns that you search against. Something like this should probably come back in less than a second...

Steve


--


--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

Reply | Threaded
Open this post in threaded view
|

Re: mysql query

David Singleton-4
It does, but when I have to run each query 130 times (the way the
previous owner has the data tables set up requires a page by page
comparison, even if the page doesn't have a record) then you're looking
at 260 seconds.

-----Original Message-----
From: Lasso Talk [mailto:[hidden email]] On Behalf Of Steve
Upton
Sent: Thursday, April 10, 2008 3:44 PM
To: Lasso Talk
Subject: Re: mysql query

At 3:16 PM -0500 4/10/08, David Singleton wrote:
>Hey list, having some problems.  I have this search and page load I
have
>that is taking over 2 minutes to load.  When looking over the code the
>biggest issue here seems to be redundancy.  I've fixed quite a few
>issues and now the biggest obstacle I have is my inexperience with
mysql

>query writing.
>
>So, here is the set up. I have two tables: pdf_Errors and
>pdf_PageHistory.   The error tables has a list of errors that each page
>had when it came to us(we're a printing company) the PageHistory table
>has a record saying when the page came in, what color it was, ext.  The
>pdf_Errors table may have many records, or no records. The
>pdf_PageHistory table may have 1 record or no records for any given
>page.
>
>When we first load up a job, I have a list of pages. (1, 3, 4, 5, 6,
>ext.) These pages are put into a "part", part is also tracked in the
>tables.
>
>Here is where I'm hitting my problem.  Its set up so as soon as the
page
>loads, they can view if the part has any errors, and if it already has
a
>history filled out for it.  Since these are in two different tables,
I'm

>having to query each one of them individually, (and once per page.)
>
>What I want, and can't seem to get (if its even possible) is something
>like this.
>
>Page      HasPDf_Error     HasPDF_Hist
>22           true                       false
>23           false                      false
>29           true                       true
>
>I'd played around with mysql using something like IF(select count(*)
>from pdf_Errors where PageNo = '22' > 0, "true", "false") as
>HasPDF_Error,
>But I'm screwing something up somewhere.  Any advice?

are you using joins at all? They will give you much higher performance.
That and indexing the columns that you search against. Something like
this should probably come back in less than a second...

Steve


--


--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/


--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

Reply | Threaded
Open this post in threaded view
|

Re: mysql query

Bil Corry-3
In reply to this post by David Singleton-4
David Singleton wrote on 4/10/2008 3:16 PM:
> What I want, and can't seem to get (if its even possible) is something
> like this.
>
> Page      HasPDf_Error     HasPDF_Hist
> 22           true                       false
> 23           false                      false
> 29           true                       true


You can use one SQL query to pull all of it, but really, if it were me, I'd just add two more columns to the table that holds all the page numbers and store the number of related records for pdf_error and pdf_hist.  That way, it simplifies the query you have to do, and it's easy enough to keep those numbers updated as you add/remove records to pdf_error and pdf_hist.


- Bil


--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

Reply | Threaded
Open this post in threaded view
|

Re: mysql query

Wade Maxfield
In reply to this post by David Singleton-4
David Singleton wrote:

> What I want, and can't seem to get (if its even possible) is something
> like this.
>
> Page      HasPDf_Error     HasPDF_Hist
> 22           true                       false
> 23           false                      false
> 29           true                       true
>
> I'd played around with mysql using something like IF(select count(*)
> from pdf_Errors where PageNo = '22' > 0, "true", "false") as
> HasPDF_Error,
> But I'm screwing something up somewhere.  Any advice?
>
>

Maybe post a sample of your query? And a bit more info on table/columns.

I'm assuming you're searching based on a job number or other unique key,
and that it is in each table pdf_Errors and pdf_PageHistory.  Is there
another table involved, say a jobs table?

Something involving joins, group by and count will most likely be the
solution.

  - Wade

--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/