Re: Query query
You should still lose the pointless WHERE 1. Arthur On Sun, Dec 4, 2011 at 1:38 AM, Jan Steinman j...@bytesmiths.com wrote: DOH! Brain unfroze, and I realized I needed an aggregate: SELECT COUNT(lib.Dewey) AS Have, ddn.Dewey AS DDN, ddn.Classification AS Classification FROM s_library_dewey ddn LEFT OUTER JOIN s_library lib ON ddn.Dewey = FLOOR(lib.Dewey) WHERE 1 GROUP BY ddn.Dewey
Re: the best way compare String
Hi Reindl, Thanks for your quick answer, but i think the engine is MySAM but I'm not sure, What type of index recomendais me, unique, full-text, etc..? Thanks a lot 2011/12/4 Reindl Harald h.rei...@thelounge.net Am 04.12.2011 20:25, schrieb Rafael Valenzuela: Hi all, In one query, I am comparing a string with a 'like=%patron%', but is very slow and expensive .What is the best way to compare a string? like or regular expression? The data base is too big table 1TB, any idea? thanks a lot http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html without indexes is big tables is no fast search possible if you are useing innodb you have currently lost -- Mit forever My Blog http://www.redcloverbi.wordpress.com My Faborite Webhttp://ocw.mit.edu/OcwWeb/Electrical-Engineering-and-Computer-Science/index.htm http://www.technologyreview.com/
Re: the best way compare String
you THINK the engine is? WTF? fulltext if you need fulltext-search please read the manuals i linked! Am 04.12.2011 21:20, schrieb Rafael Valenzuela: Hi Reindl, Thanks for your quick answer, but i think the engine is MySAM but I'm not sure, What type of index recomendais me, unique, full-text, etc..? Thanks a lot 2011/12/4 Reindl Harald h.rei...@thelounge.net Am 04.12.2011 20:25, schrieb Rafael Valenzuela: Hi all, In one query, I am comparing a string with a 'like=%patron%', but is very slow and expensive .What is the best way to compare a string? like or regular expression? The data base is too big table 1TB, any idea? thanks a lot http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html without indexes is big tables is no fast search possible if you are useing innodb you have currently lost signature.asc Description: OpenPGP digital signature
Re: the best way compare String
Rafael, Without more details it is hard how you can optimize a query. 1. even though database 1TB, what about the table on which your running this query? Do you have any index on the column? How may chars long is the column? etc. 2. Run the explain and see what is the optimizer is telling - using key or not, full scan or not, etc. 3. see whether you can change the like to 'ABCpatron%'. See - http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html - The index also can be used for LIKEhttp://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_likecomparisons if the argument to LIKEhttp://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_likeis a constant string that does not start with a wildcard character. 4. you may want to take subset of data and try both RegEx and like. Compare them. 5. Also, see if you can make use of prefix-indexing where you only first N characters are used. Best, Shiva On Sun, Dec 4, 2011 at 11:25 AM, Rafael Valenzuela rav...@gmail.com wrote: Hi all, In one query, I am comparing a string with a 'like=%patron%', but is very slow and expensive .What is the best way to compare a string? like or regular expression? The data base is too big table 1TB, any idea? thanks a lot -- Mit forever My Blog http://www.redcloverbi.wordpress.com My Faborite Web http://ocw.mit.edu/OcwWeb/Electrical-Engineering-and-Computer-Science/index.htm http://www.technologyreview.com/
Re: Query query
Well, of that which you showed you _need_ only this, not really different from that which you wrote: SELECT COUNT(lib.Dewey) AS Have, ddn.Dewey AS DDN, ddn.Classification FROM s_library_dewey ddn LEFT OUTER JOIN s_library lib ON ddn.Dewey = FLOOR(lib.Dewey) GROUP BY ddn.Dewey As for FLOOR in an ON-clause, surely the general-builtin-function overhead completely overwhelms the operation s cost. Maybe index on Dewey would help. (Which Dewey? with computer under math, c, or with computer under 000? Where can one get a 1000-element list for computer?) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
