Jump to content
  • Advertisement
Sign in to follow this  

[web] MySQL Optimization Question

This topic is 4068 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

I have a MySQL table: (pseudo-code) ListingFieldData(ParentListingID INT, Value LONGTEXT) A basic search queries the Value, and returns all of the ParentListingIDs relevant to the query. (There may be 30+ ListingFieldData rows per each ParentListingID - and possibly thousands of "Parent Listings" - meaning potentially hundreds of thousands of ListingFieldData rows in total.) * The problem is that the search only needs to return each ParentListingID once, instead of hundreds of thousands of times. I've experimented with both the "DISTINCT" and "GROUP BY" keywords, both to no avail. The GROUP BY is the closest I've gotten, but it doesn't seem to work the way I need it to (I'm probably using it wrong). I have a feeling that I'm grossly overlooking something...something simple... Question) is there a way to get MySQL to return only "distinct" ParentListingIDs, skipping ParentListingIDs that have already been found? Perhaps there is some sort of "DISTINCT COLUMN" functionality? (Note: I need this operation to be performed MySQL-server-side, instead of "manually" trimming duplicate ListingFieldIDs with a PHP routine) Thanks for any help. [Edited by - swordfish on September 29, 2007 8:20:13 PM]

Share this post

Link to post
Share on other sites
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 ..)

Share this post

Link to post
Share on other sites
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?

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]

Share this post

Link to post
Share on other sites
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:

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.

Share this post

Link to post
Share on other sites
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.

Share this post

Link to post
Share on other sites
Sign in to follow this  

  • Advertisement

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!