homepage Welcome to WebmasterWorld Guest from 50.19.169.37
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
miracle




msg:3273150
 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.

 

justageek




msg:3273183
 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?

JAG

miracle




msg:3273185
 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"?

LifeinAsia




msg:3273191
 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