Re: Fwd: Re: using a php page to reboot linux
References: <[EMAIL PROTECTED]> Mail-Copies-To: nobody X-PGP-Fingerprint: E394 1C70 C7F9 B740 CD2E FAC2 18CD 25A5 22F3 8AF3 Organization: LinuxFr - http://linuxfr.org Date: Thu, 18 Jul 2002 01:28:26 +0200 In-Reply-To: <[EMAIL PROTECTED]> ([EMAIL PROTECTED]'s message of "Thu, 18 Jul 2002 00:48:13 +0200") Message-ID: <[EMAIL PROTECTED]> Lines: 12 User-Agent: Gnus/5.090007 (Oort Gnus v0.07) Emacs/21.2 (i386-debian-linux-gnu) MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii sql,query > Are you sure you want to do that? > Since php can call local programs, you can call one to reboot your computer. > I think it's not a good idea to allow it. It might be fun for us, he just has to not forget to drop us the url ;-) -- 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
select tuning / simple
Hi! I got a very simple SQL query which takes 0.2sec to execute (far too long) and it seems I can't do nothing about it. I use : "SELECT id FROM news WHERE state=1 and main_page=1 order by timestamp desc LIMIT 10" I got indexes too : +---+++--+-+---+-+--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | |Cardinality | Sub_part | Packed | Comment | +---+++--+-+---+-+--++-+ | news | 0 | PRIMARY|1 | id | A | |8433 | NULL | NULL | | | news | 1 | state |1 | state | A | |4 | NULL | NULL | | | news | 1 | main_page |1 | main_page | A | |1 | NULL | NULL | | | news | 1 | timestamp |1 | timestamp | A | |22 | NULL | NULL | | | news | 1 | main_page2 |1 | main_page | A | |1 | NULL | NULL | | | news | 1 | main_page2 |2 | state | A | |6 | NULL | NULL | | | news | 1 | main_page2 |3 | timestamp | A | |68 | NULL | NULL | | +---+++--+-+---+-+--++-+ I did read the doc and I understood this is slow because I use the ORDER BY, but isn't it a way to speed this up a little bit ? Thanks. -- 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
Re: optimisation trouble
Bonjour mysql! Tue, 21 May 2002 00:30:38 +0200, tu as dit : > Your message cannot be posted because it appears to be either spam or > simply off topic to our filter. To bypass the filter you must include > one of the following words in your message: > sql,query > If you just reply to this message, and include the entire text of it in the > reply, your reply will go through. However, you should > first review the text of the message to make sure it has something to do > with MySQL. Just typing the word MySQL once will be sufficient, for example. > You have written the following: > Mon, 20 May 2002 16:06:09 +0200, tu as dit : >> * Fabien Penso >> [...] >>> Not that large, I got about 8.000 records in each table. I did put the >>> body out from the news table, so the news table could be small, and >>> news_body which I don't request all the time would be the bigger one. >> from your original post: >> FROM news_body,news, >> Try ... FROM news, news_body, > I had tried this already, but it makes no change (I did read this in the > doc too). > I have tried many things, still didn't find any solution. Anyone ? > -- > Fabien Penso <[EMAIL PROTECTED]> | LinuxFr a toujours besoin de : > http://perso.LinuxFr.org/penso/ | http://linuxFr.org/dons/ -- 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
Re: optimisation trouble
Mon, 20 May 2002 08:26:31 -0500, tu as dit : > [snip] >> How many records do you have in each table? And just how fast do you want >> this query to be? 1.5 seconds may be optimal for a query this large. > Perhaps >> the only thing you could do at this point is beef up your server. > Not that large, I got about 8.000 records in each table. I did put the > body out from the news table, so the news table could be small, and > news_body which I don't request all the time would be the bigger one. > If I do 2 selects, one to fetch info from the news table, and then > another one to fetch bodies, it takes much less time, like 0.05 for each > request. Did I miss something ? > [/snip] > You don't say anything about your hardware, which could be a portion of the > problem. If you combine the two queries you have a total return time of 1.0 > secs, right? The other 0.5 secs (return time varies with time of day, > traffic etc.) is probably processing overhead. Why is getting below 1.5 secs > so important? Just curious... No, it takes 0.08+0.07 (just tried) so it makes it 0.15sec instead of 1.5sec, that's 10 times more... 0.15 is ok for a huge site, 1.5 isn't (to me). (I said 0.05sec, not 0.5sec :-) -- 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
Re: optimisation trouble
Hi Jay! Mon, 20 May 2002 07:45:00 -0500, tu as dit : > [snip] > I have just tried this, but it still takes 1.5sec to run the > select... :-( > [/snip] > Fabien, > How many records do you have in each table? And just how fast do you want > this query to be? 1.5 seconds may be optimal for a query this large. Perhaps > the only thing you could do at this point is beef up your server. Not that large, I got about 8.000 records in each table. I did put the body out from the news table, so the news table could be small, and news_body which I don't request all the time would be the bigger one. If I do 2 selects, one to fetch info from the news table, and then another one to fetch bodies, it takes much less time, like 0.05 for each request. Did I miss something ? I could still use thoses 2 requests, but I'd prefer to merge them into one, so I don't have to use temporarly variables. -- 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
Re: optimisation trouble
Hi! Mon, 20 May 2002 21:30:52 +0900, tu as dit : > Hi, You are look like very tired... > I have a good idea!!! > See fellowing line. "alter table TABLE_NAME add index (timestamp > desc);" > import point is "index(timestamp desc)". you did not set index of > timestamp, so your query has been slow > You can your query fast by adding index timestamp and you must add > 'desc' after 'timestamp' > The reason to add 'desc' is that index of timestamp is ordered > automatically and rebuilded index structure when table is inserted some > data... > So... Complete script is "alter table TABLE_NAME add index (timestamp > desc); "; I have just tried this, but it still takes 1.5sec to run the select... :-( -- 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
optimisation trouble
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
