[Tfug] MySQL hangs on a large query

[Thomas Tarbet]

Just to see the difference that indexes can make I did a little test.  The test had the same tables created twice.  One time with and one time without using primary key indexes and that same query you gave with tables populated with the same number of rows 2100 and 37000.

The results are:

Without indexes the query took 137 seconds,

and with primary key indexes it took 0.04 seconds....

Over 2 minutes versus less then 1/10 of a second.



----- Original Message ----
From: Jeff Breadner <jeff at breadner.net>
To: tfug at tfug.org
Sent: Friday, May 23, 2008 3:09:35 PM
Subject: Re: [Tfug] MySQL hangs on a large query

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://tfug.org/pipermail/tfug_tfug.org/attachments/20080523/19ff74d2/attachment-0002.html>