homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

query times out
large table

10+ Year Member

Msg#: 3273148 posted 9:24 pm on Mar 6, 2007 (gmt 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.



WebmasterWorld Senior Member 10+ Year Member

Msg#: 3273148 posted 9:57 pm on Mar 6, 2007 (gmt 0)

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



10+ Year Member

Msg#: 3273148 posted 10:00 pm on Mar 6, 2007 (gmt 0)

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


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

Msg#: 3273148 posted 10:12 pm on Mar 6, 2007 (gmt 0)

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]

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved