[web] MySQL Optimization Question

That's odd, what is your SQL statement?

Using something like the below should only give you each matching ParentListingID once.

SELECT DISTINCT ListingFieldData.ParentListingID
FROM ListingFieldData
WHERE (.. some matching criteria ..)

I think DISTINCT works as advertised - not sure what went wrong.
Either way, I'm still having issues.

Here's the query:

(A) SELECT SQL_BIG_RESULT DISTINCT(listingID) FROM vamm_v1a1_listing_field_data

The above returns only 1 listingID, which appears to either be the very last listingID, or the count of all of the listingIDs. I'm 100% sure that there are more than 1 distinct listingIDs.

The above is a "browse all" type of search.

On the other hand, if I add WHERE criteria to the query, the situation is drastically different:

(B) SELECT SQL_BIG_RESULT DISTINCT(listingID) FROM vamm_v1a1_listing_field_data WHERE (sectionFieldID='1' AND value_string='Existing')

That last query (B) appears to work the way I want it to, returning thousands of distinct listingIDs. Perfect, but (A) is still flawed. At this point, I can workaround (A) and just simply fetch all of the listingIDs directly from the Listings table, but I'd still like to solve (A) for peace of mind.

What am I not seeing?

P.S.
Another test:

(C) SELECT SQL_BIG_RESULT listingID FROM vamm_v1a1_listing_field_data GROUP BY listingID

(C) appears to have the same result as (A)...

Also, if I remove the SQL_BIG_RESULT keyword from the query, the search yields a 500 Internal Server Error (which is my original problem; and none of the php.ini directives remedies the problem).

[Edited by - swordfish on September 29, 2007 10:18:00 PM]

How are you issuing commands to the database, by running the mysql program through a command shell/prompt or through an database driver or language binding?

If you are not running through a command shell/prompt, I suggest doing so to eliminate any issue with language bindings.

Also a quick search on google reveals there is was bug with the SQL_BIG_RESULT hint so you might want to check your version of MySQL.

Also try running this query:

SELECT SQL_BIG_RESULT DISTINCT(listingID)
FROM vamm_v1a1_listing_field_data
WHERE (listingID=listingID)

Notice that the WHERE clause technically does nothing as it should return all values (assuming the listingID field doesn't allow nulls), but maybe it generates a different execution plan that might return the correct results.

I'm issuing the commands via PHP.
I've been trying to get ahold of my client for the past few days so I can access their PhpMyAdmin, but communication is slow.

MySQL 5.0.18, PHP 4.3.11 (GoDaddy Shared Hosting)

I originally tried the listingID=listingID as illustrated by a site found on Google, and it doesn't seem to make any difference.

I can get rid of the 500 Internal Server Error by throwing a LIMIT into the mix.
What does this mean? Perhaps I'm running out of memory? I tried boosting the max_execution_time, mysql.connect_timeout, and memory_limit directives, nothing works.

I'll try to issue the commands via PhpMyAdmin whenever I get a chance.