Re: 45 seconds
Check that your fields are indexed. This usually fixes it. George - Original Message - From: Elsad YUSIFLI [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, June 10, 2002 3:10 PM Subject: 45 seconds i have a table and 51000 records in it. it has got an index on HOST_NAME field. next query lasts 45 seconds to execute... is it normal ? server is PIII 500 double cpu SELECT b.ip as ip, b.country , b.hostname , a.HOST_NAME as host, COUNT(a.HOST_NAME) as number FROM new_raw_log as a, dns as b WHERE (a.HOST_NAME regexp rtrim(b.ip)+'%') AND a.GID=44 AND a.SID=12 GROUP BY a.HOST_NAME ORDER BY number DESC LIMIT 0,20 - 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 - 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: 45 seconds
Hello, The reason that it takes so long is because you are using regexp on a function to join the table. That means that MySQL can't use indexes for that column. If you run an EXPLAIN on the query you will see that is true. To speed up the query some you could add an index on new_raw_log(GID,SID). That will at least allow some filtering using an index and might give some speed up, depending on how many matching results there are. The ideal case would be redesign your database to be able to join the tables using some other mechanism than a regular expression statement which MySQL will then be able to use indexes to join on. Harrison Elsad YUSIFLI wrote: i have a table and 51000 records in it. it has got an index on HOST_NAME field. next query lasts 45 seconds to execute... is it normal ? server is PIII 500 double cpu SELECT b.ip as ip, b.country , b.hostname , a.HOST_NAME as host, COUNT(a.HOST_NAME) as number FROM new_raw_log as a, dns as b WHERE (a.HOST_NAME regexp rtrim(b.ip)+'%') AND a.GID=44 AND a.SID=12 GROUP BY a.HOST_NAME ORDER BY number DESC LIMIT 0,20 - 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 - 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: 45 seconds
I have multiple tables on a 166MMX with 64MB ram, all containing more than 250,000 records each Mine takes less than 5 seconds I think there's something wrong at your side :-) Kind Regards, Chris Knipe MegaLAN Corporate Networking Services Tel: +27 21 854 7064 Cell: +27 72 434 7582 - Original Message - From: Elsad YUSIFLI [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, June 10, 2002 4:10 PM Subject: 45 seconds i have a table and 51000 records in it. it has got an index on HOST_NAME field. next query lasts 45 seconds to execute... is it normal ? server is PIII 500 double cpu SELECT b.ip as ip, b.country , b.hostname , a.HOST_NAME as host, COUNT(a.HOST_NAME) as number FROM new_raw_log as a, dns as b WHERE (a.HOST_NAME regexp rtrim(b.ip)+'%') AND a.GID=44 AND a.SID=12 GROUP BY a.HOST_NAME ORDER BY number DESC LIMIT 0,20 - 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 - 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: 45 seconds
Try using LIKE instead of regexp and create index on HOST_NAME if not present. Nilesh -Original Message- From: Chris Knipe [mailto:[EMAIL PROTECTED]] Sent: Monday, June 10, 2002 1:24 PM To: Elsad YUSIFLI; [EMAIL PROTECTED] Subject: Re: 45 seconds I have multiple tables on a 166MMX with 64MB ram, all containing more than 250,000 records each Mine takes less than 5 seconds I think there's something wrong at your side :-) Kind Regards, Chris Knipe MegaLAN Corporate Networking Services Tel: +27 21 854 7064 Cell: +27 72 434 7582 - Original Message - From: Elsad YUSIFLI [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, June 10, 2002 4:10 PM Subject: 45 seconds i have a table and 51000 records in it. it has got an index on HOST_NAME field. next query lasts 45 seconds to execute... is it normal ? server is PIII 500 double cpu SELECT b.ip as ip, b.country , b.hostname , a.HOST_NAME as host, COUNT(a.HOST_NAME) as number FROM new_raw_log as a, dns as b WHERE (a.HOST_NAME regexp rtrim(b.ip)+'%') AND a.GID=44 AND a.SID=12 GROUP BY a.HOST_NAME ORDER BY number DESC LIMIT 0,20 - 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 - 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 - 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
