Hi guys

I'm actually getting some success.

The "OR EXISTS" is actually valid.  Some resources are linked in a heirarchical fashion, so a resource is usually a physical entry, however sometimes it is a virtual entry linking to 2 or more other physical resources (hence the 'link' table).  So when looking for these bookings we look for the physical resource number or the logical resource that is linked to the physical resource number.  It works.

Anyways, I didn't see it at first, but the problem turned out to be the time comparisons.  If you have a million records in your database and look for A >= 27 AND B <= 31 the database still has to pull in a lot of data to perform the comparison necessary.   So I calculated some more times (limits on how long a booking can be) and was able to rewrite the WHERE clause as A BETWEEN 27 AND 40 AND B BETWEEN 20 AND 31.  Suddenly those queries have disappeared from the log.  It is likely now they are getting direct index hits or at most scanning 10-20 records.

So now I have my linux session running: tail -f /var/log/mysql/mysql-slow.log
Then I have phpMyAdmin running in my browser
Now I just wait for entries to appear that have scanned a significant number of records and add an appropriate index to the table.  Wait for the next query.

It's pretty cool and has certainly dealt with the worst of the performance issues (the ones that were occurring frequently).

I still need to turn the Propel logging on and deal with the Join issues..... maybe tomorrow.

Thanks for your help guys.

Cheers
Murray

2009/7/22 Alan Pinstein <apinstein <at> mac.com>

I am not really a mysql expert (I use postgres) but that OR EXISTS looks odd to me... what does that do? I'd also use mysql's EXPLAIN command.

On Jul 21, 2009, at 6:21 PM, Murray Collingwood wrote:

Thanks for your comment Alan - it's rather tedious work but you are right, there are most likely a number of times where I have been lazy and not bothered with the 'Join' option.

Anyways, I turned on log_queries_not_using_indexes and started getting some interesting results.  Here is an example:

# Time: 090722  8:14:15
# User <at> Host: sobs[sobs] <at> localhost []
# Query_time: 0  Lock_time: 0  Rows_sent: 0  Rows_examined: 325503
SELECT booking.*
   FROM booking
WHERE booking.timestart <= FROM_UNIXTIME(1248404399)
      AND booking.timeend >= FROM_UNIXTIME(1248400801)
      AND (
               booking.resourceid = 0
               OR EXISTS (
                                 SELECT *
                                    FROM link
                                 WHERE link.parentid = 0        
                                      AND link.resourceid = booking.resourceid
                                 )
             )
ORDER BY booking.timestart;


(I did the reformatting of the query so it is easier to read).  Booking.Timestart, Booking.Timeend and Link.resourceid I have manually added an index to these fields.   Booking.Resourceid and Link.parentid had existing Propel defined foreign indexes.

How come this is scanning 325,503 rows?

Cheers
Murray

2009/7/21 Alan Pinstein <apinstein <at> mac.com>

> Because I'm using Propel it is very easy to be performing lots of
> small queries (without always knowing you are) and so I believe the
> queries causing me problems are only taking a few milliseconds
> however they maybe scanning a few thousand records in order to
> produce a result.  How do I locate these?

You are very right; if you use propel wrong what should be 1 query can
end up as 1 + N queries, particularly when fetching related records.

I would recommend you turn on full query logging on a dev server and
walk through all of your pages to see how many queries they are
executing.

For instance, a page might do something like:

$author = AuthorPeer::retrieveByPK();
foreach ($author->getBooks() as $book) {
  print "Editions: " . join(',', $book->getEditions());
}

This would do lots of queries and would be improved with:

$author = AuthorPeer::retrieveByPK();
foreach ($author->getBooksJoinEditions() as $book) {
  print "Editions: " . join(',', $book->getEditions());
}

Stuff like that.

The latest svn version of propel has some new query logging tools that
will actually log exact SQL that you can copy/paste in your database
to check these things.

Alan

------------------------------------------------------
http://propel.tigris.org/ds/viewMessage.do?dsForumId=1097&dsMessageId=2372949

To unsubscribe from this discussion, e-mail: [users-unsubscribe <at> propel.tigris.org].

--
Murray Collingwood
Focus Computing
p +61 415 24 26 24
http://www.focus-computing.com.au

--
Murray Collingwood
Focus Computing
p +61 415 24 26 24
http://www.focus-computing.com.au