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.
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]