[Tfug] MySQL hangs on a large query

[Nate]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Indexes on zsis_rt1.College and appointment.Date will also help because
you're sorting by those.  You could also try throwing 'explain' in front
of the query to see if it is doing a full table scan.  Are
appointment.Student and zsis_rt1.StudentID both primary keys (or at
least unique)?  If not, then you probably have a cartesian join going.

I would probably try to reduce the query down to its basics to determine
where the problem lies.  Remove the order by first, then like Jeff said,
change it from an OUTER to an INNER join.  Then check queries against
each table individually to make sure there isn't something going on with
the tables (or rows) themselves like a hung lock.

Nate

Jeff Breadner wrote:
| Claude Rubinson wrote:
|> On Fri, May 23, 2008 at 02:24:35PM -0700, Christopher Robbins wrote:
|>
|>> Has anyone had problems with MySQL queries getting "stuck"?
|>>
|>> I have a good sized query I need to run on two tables, includes a
|>> left join, and running it in either phpMyAdmin or from the command
|>> line gives me nothing.  Both browser/command prompt wait for
|>> something, but nothing seems to be returned.  The tables are an
|>> appointment table (2100+ records) and a table of students (37000+
|>> records).
|>>
|> Hrm... that's pretty small and shouldn't be a problem.  Are you
|> certain that you don't have a data problem (wonky data, bad indices,
|> etc)?  What happens if you rewrite the LEFT JOIN as an INNER JOIN?
|> (Given your size, this shouldn't be a problem but outer joins can be
|> notoriously slow depending upon the db schema.)
|>
|> C.
|
| An index on each of appointment.Student and zsis_rt1.StudentID might
| help too.
|
| Does 'top' show your system as being busy when it hangs like this?  If
| you copy all of this to a test database and cut out 50% or 90% of the
| rows from each table, does it start working?
|
|   JB
|
|
| _______________________________________________
| Tucson Free Unix Group - tfug at tfug.org
| Subscription Options:
| http://www.tfug.org/mailman/listinfo/tfug_tfug.org

- --
Nate
System Admin Manager
System Administration
Ext 220
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2.2 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFIN0ZVRMRYK1K/wKQRAlXdAJ49I3zs9PkE7VE8nSh0dFbO3u6feACgl5vJ
3m+az7Z+LS6WvZPIaWqJOKw=
=Vaxi
-----END PGP SIGNATURE-----