Welcome to WebmasterWorld Guest from 54.224.108.189

Forum Moderators: open

Message Too Old, No Replies

query times out

large table

     
9:24 pm on Mar 6, 2007 (gmt 0)

Junior Member

10+ Year Member

joined:Jan 18, 2005
posts:65
votes: 0


Here is the query:

SELECT spider.*,mt.mt_id, mt.mt_hasattach FROM ibf_spider_logs spider LEFT JOIN ibf_message_topics mt ON ( mt.mt_msg_id=spider.spider_mtxt_id ) ORDER BY spider_date DESC;

The table has 42,000 rows and that query times out, does not complete. I had to kill it using mytop in ssh. However, when I emptied the ibf_spider_logs table and waited a few hours for some rows to be created, that query ran without a problem.

What do I need to modify in that query to make it work with large tables? Thanks in advance.

9:57 pm on Mar 6, 2007 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Aug 21, 2003
posts:1069
votes: 0


I'm guessing it is the order by not having an index? Can you post the table(s) create sql?

JAG

10:00 pm on Mar 6, 2007 (gmt 0)

Junior Member

10+ Year Member

joined:Jan 18, 2005
posts:65
votes: 0


So it needs an index due to many rows? What do you mean by "post the table(s) create sql"?
10:12 pm on Mar 6, 2007 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5682
votes: 79


Having indexes on ibf_message_topics.mt_msg_id and ibf_spider_logs.spider_mtxt_id would probably help as well. Also, are ibf_message_topics.mt_msg_id and ibf_spider_logs.spider_mtxt_id INT fields?

What justageek means is posting the SQL code used to create the table so people can look at the table structure, field types, indexes, etc. and make additional suggestions for speeding things. It's very strange that a query is timing out with only 42K rows.

[edited by: LifeinAsia at 10:15 pm (utc) on Mar. 6, 2007]