Hi!
I got a problem I'm trying to fix with no luck. I got 2 tables : CREATE TABLE news ( id int(6) NOT NULL auto_increment, topic_id int(3) NOT NULL, section_id int(3) NOT NULL, author_id int(6) NOT NULL, lang_id int(2) NOT NULL, user_id int(6) DEFAULT '0' NOT NULL, state int(1) DEFAULT '0' NOT NULL, main_page int(1) DEFAULT '0' NOT NULL, timestamp timestamp(14), title varchar(150) DEFAULT '' NOT NULL, departement tinytext, ip varchar(9) NOT NULL, KEY id (id,topic_id,section_id,author_id) ); CREATE TABLE news_body ( id int(6) NOT NULL auto_increment, body blob NOT NULL, content_type varchar(16) DEFAULT 'text/plain' NOT NULL, KEY id (id) ); (and few more but it doesn't matter). A select gives me : desc SELECT news.id,news.timestamp,news.title,news.departement,news.author_id,news_body.body,news_body.content_type,topics.topic,sections.section,news_author.name,news_author.contact FROM news_body,news,topics,sections,news_author WHERE news.id=news_body.id AND news.author_id=news_author.id AND news.topic_id=topics.id AND news.section_id=sections.id AND news.state='1' AND news.main_page='1' order by news.timestamp desc LIMIT 10; +-------------+--------+---------------+---------+---------+-----------------+------+---------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | |Extra | +-------------+--------+---------------+---------+---------+-----------------+------+---------------------------------+ | news_body | ALL | PRIMARY | NULL | NULL | NULL | 7853 | |Using temporary; Using filesort | | news | eq_ref | PRIMARY | PRIMARY | 4 | news_body.id | 1 | |where used | | topics | eq_ref | PRIMARY | PRIMARY | 4 | news.topic_id | 1 | | | | sections | eq_ref | PRIMARY | PRIMARY | 4 | news.section_id | 1 | | | | news_author | eq_ref | PRIMARY | PRIMARY | 4 | news.author_id | 1 | | | +-------------+--------+---------------+---------+---------+-----------------+------+---------------------------------+ and it takes about 1.5sec to run. If I try without the order by news.timestamp desc then it takes 0.08sec. I tried things like : alter table news add index timestamp(timestamp) but it doesn't make things faster. I did read the mysql documentation, but I don't see how to fix that. Would it be better to put the body into the news table ? If anyone has an idea, feel free to say so :-) (except to have 2 selects, one for the news, and another for the news_body table). -- Fabien Penso <[EMAIL PROTECTED]> | LinuxFr a toujours besoin de : http://perso.LinuxFr.org/penso/ | http://linuxFr.org/dons/ --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
