1

I have three tables

listings: id, pid, beds, baths, etc, etc, etc, db
locations: id, pid, zip, lat, lon, etc, etc, etc, db
images id, pid, height, width, raw, etc, etc, db

id, pid & db are indexed. db just references the mls provider a particular item came from. in images the raw column holds raw image data

there are about 15k rows in listings/locations, and about 120k rows in images so there are multiple rows that have the same pid.

when i do "select pid from listings" or "select pid from locations" the query completes successfully in about 100ms.

when i do "select pid from images" it just hangs in sqlyog and never completes... i was thinking since the raw column contains alot of information that it might be trying to select that too, but my query doesn't try to select that so I can't imagine why it's taking so long...

any idea why this is happening??

Mickey
  • 2,285
  • 6
  • 26
  • 37
  • Have you tried simple troubleshooting like 'select pid from images limit 10;" ? If so, how long does that take? – MJB Apr 11 '10 at 20:26
  • limit 10, limit 100, limit 1000, limit 10000 work fine.. when i do limit 100000 it does the same thing the regular query does when a limit is not specified. - which is it runs forever then when it finally completes, it says it took 1.3 seconds - when in actuality it took several minutes. – Mickey Apr 11 '10 at 20:36
  • 3
    Sounds strange. Maybe a broken index or data table? Can you try dumping the whole table and re-inserting it in a different database? – Pekka Apr 11 '10 at 20:41
  • I was actually going to try that but put it all on a different server just to see. Is there a way to check and see if the index is indeed broken? – Mickey Apr 11 '10 at 20:50
  • 1
    Maybe `CHECK TABLE` finds some problems. http://dev.mysql.com/doc/refman/5.1/en/check-table.html – VolkerK Apr 11 '10 at 22:37
  • run `mysqladmin -pr` and see what it's doing – nos Apr 11 '10 at 22:39
  • @volkerk check table returned status=ok, @nos is there a way to write that in a query? i don't use cmd. @pekka would re-building the index work as well? – Mickey Apr 11 '10 at 23:02
  • btw: Why do you want to select/fetch all 120k ids? The answer may very well be that you simply want to know _why_ it's slow. Just curious. Oh, and you might want to ask all those brilliant database admins that question on http://serverfault.com as well ;-) Don't forget to tell thm exactly how you fetch the results (i.e. whether it's a buffered or an unbuffered query) – VolkerK Apr 12 '10 at 00:45
  • i have to do a cron every night.. it re-imports all the listings, all over again.. but it doesn't re-get the images.. so after it inserts all the listings, it does a query on the image table to get all the pids, then it does a "update listings set image_update=1 where pid in(implode(pids from images))" to let the listings know they already have images before trying to get images for the listings that don't have any yet. -- doesn't make that much sense, but different people have different a$$ backwards ways of being RETS compliant - so i had to do what i had to do to make it all work. – Mickey Apr 12 '10 at 01:11
  • Wow, at the very least that `in(implode(pids from images))` sounds wrong. You should avoid fetching lots of data that you only have to send back for another query. I suggest you ask _that_ as a separate question, explaining in more detail why and how you need to re-import the data. – VolkerK Apr 12 '10 at 01:38
  • @pekka - i rebuilt the index on the pid in the images table, everything is working great now. thanks --- @volkerk, i'm well aware, it's poorly designed because i'm working with a poorly designed specification that was poorly implemented by a third party. the question was why does the query take 10 times longer than it's supposed to.. but thanks for trying to help. – Mickey Apr 12 '10 at 02:37
  • Nevertheless transferring all those pids from MySQL to your php process only to transfer them back is most certainly unnecessary. Not doing so (while achieving the same result as if your approach was working) will probably solve your problem. If you're unable or unwilling to touch the code at all ...then it's clearly a question for serverfault.com in my book. – VolkerK Apr 12 '10 at 03:13
  • @pekka, if you post your "answer" i'll accept it as an answer if you care about SO points and what not. – Mickey Apr 13 '10 at 02:41

1 Answers1

0

Maybe a broken index or data table? Can you try dumping the whole table and re-inserting it in a different database?

(See extended comments and input in the comments)

Pekka
  • 442,112
  • 142
  • 972
  • 1,088