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
