Welcome to WebmasterWorld Guest from 54.145.44.134

Forum Moderators: open

Message Too Old, No Replies

query times out

large table

     

miracle

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

10+ Year Member



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.

justageek

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

WebmasterWorld Senior Member 10+ Year Member



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

JAG

miracle

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

10+ Year Member



So it needs an index due to many rows? What do you mean by "post the table(s) create sql"?

LifeinAsia

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

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



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]

 

Featured Threads

Hot Threads This Week

Hot Threads This Month