[Tfug] MySQL hangs on a large query

[Jeff Breadner]

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