I am doing a server application to check the password for complexity.

In the MySQL database, I have a table containing about 100,000 popular passwords. For example:

+----+-----------+ | id | words | +----+-----------+ | 1 | 123456 | | 2 | password | | 3 | 12345678 | | 4 | qwerty | | .............. | +----+-----------+ 

The user enters the password " 132password443 ". A match must be found in the database, in this case it corresponds to " password ".

I have to make a request through the concatenation of rows of a column, which shows a very slow performance:

 SELECT * FROM mytbl WHERE '132password443' LIKE CONCAT('%',words,'%') 

I would like to increase the speed of query execution, but I do not know how to do it! Perhaps there is a more rational database query?

Or maybe advise to use some other database engine, where it is easier to implement such a sample?

Thank you in advance!

  • Not a single database engine has serious optimization tools for searching for a substring in a string. The maximum possible - replacing LIKE with WHERE LOCATE(word,password) > 0 is also not very good, but at least there is less overhead ... Use an external search engine. - Akina
  • @Akina, Thanks for the recommendation! And what external search engines do you mean? Which for example? - Pavel
  • Sphinx, ElasticSearch, etc. - Akina
  • @Akina, thanks! - Pavel
  • @Akina Installed Sphinx, began to understand, it turns out he does not support either CONCAT ('%',words,'%') or LOCATE(word,password) > 0 . Maybe there are other solutions? - Pavel

0