Bug #13142 Table Join - Large Speed Difference

• Submitted: 13 Sep 2005 14:56
• Modified: 13 Sep 2005 16:40
• Reporter: Jim Burnham
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1
• OS: Windows (Win 2K)
• CPU Architecture: Any

[13 Sep 2005 14:56] Jim Burnham

Description:
When running this query -

SELECT * FROM tblManual INNER JOIN tblManualGroup ON tblManualGroup.manualID = tblManual.manualID WHERE tblManualGroup.categoryGroupID = 44 ORDER BY tblManualGroup.displayOrder

tblManual contains about 1500 records with manualID as the Primary key.  

tblManual group has about 1200 records with a Primary key and an Index created on the manualID column.

We're seeing a very large speed difference when executing this as an in-line ASP query and the execution in the Query Browser.

The Query Browser returns the results in less than 1 second, the in-line ASP is taking between 10 and 20 seconds to return the results.

We are using a DSN-less connection string, client-side cursor, adOpenStatic and adLockOptimistic.

This query was running on MS SQL 2000 without any problems and SQL 2K would return the result in less than 1 second.

How to repeat:
If needed, we can send you the tables.

[13 Sep 2005 16:40] MySQL Verification Team

We're sorry, but the bug system is not the appropriate forum for 
asking help on using MySQL products. Your problem is not the result 
of a bug.

Support on using our products is available both free in our forums
at http://forums.mysql.com and for a reasonable fee direct from our
skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.