tahtu

I found the problem, it was my mistake.

I wrote a message to your at this page: https://fulltextsearch.org/contact/

Maybe this is not the right place for a further discussion about WPFTS…

@epsiloncool I’m using MyISAM, since my ISP didn’t update MySQL since some years. He still offers MySQL 5.5. But I have that account for free. So it’s Ok in the moment. But with your information about the 4 needed characters for MyISAM right now I have a reason to change the ISP – or using any plugin.

Ok, I will test WPFTS.

Until now I thought every fulltext index usage with MATCH … AGAINST is much faster than any LIKE ‘ego%’. Am I wrong?

@epsiloncool do you know a way to put some field from the WP_Query result into the global $post variable? I would like to see the score of the relevance on result list. As far as I understood the output of Better Search @ajay don’t know it, but the developer of Relevanssi knows the way.

Indeed indexed search has a lot of advantages. For example the speed / resource usage. And an own index has additional advantages, for example the option to index attachments too. The possiblility to weight the placment of the search hit is important too, I think.

But I still think: There is not one solution, which is the best one.

For example, I searched the word “ego” on my site right now. I feel, it should be one of the most used and most important words on it. But the MySQL fulltext search didn’t show me any post. Now, I try to find out why. Maybe with 3 characters, this word is too short for the fulltext index?

Indeed I would be able to configure it somewhere. But where? In the my.ini, I can’t access? Inside the CREATE TABLE definition, I don’t know a way to place this length definition of the word length…

But since this word is very important on this site, not to find posts with it is also a bad solution. How do you handle this problem with WPFTS?

I’m happy about this discussion too. I hope nobody feels bothered about we do this here, inside the Better Search area… ??

@epsiloncool like I mentioned before, I want to search inside the excerpt field (and indeed content and title). Today, I don’t have any attachment, custom fields and it’s not important to search in any taxonomy.

So in my case, I still think my simple MATCH ... AGAINST implementation is for the moment the best one. But I’m thinking about a translation of the whole site. In that case, I need a different search, depended on the current selected language.

Indeed this is not needed today or tomorrow, but it’s easier for me to find a solution than, if I do not implement an other complex solution before.

Once again: Imho, there is not right solution. Indeed speed is a great problem for every search. So if I get trouble with it, I have to think about that again. The Better Search plugin seems not be optimized for speeding, since it combined BOOLEAN and NATURAL modes together. Addition, there is and unneeded index used, which takes unneeded storage uses.

Maybe your solution would be better for that.

But in the moment, I’m working on two machines: One development and post writing, and one live working one. It’s easier for me to transfer the data, if I don’t have to rebuild the index each time manually. MySQL rebuilds his index automatically for me.

And you are right: I don’t have access to the my.ini. Nevertheless I think, WordPress should implement the same solution, I implemented. This would take very few additional code for them and just a few options inside the settings. The user could decide to use the MySQL FULLTEXT index or not. If yes, he could select about date or relevance ordering. And if wanted, he could use the BOOLEAN mode instead of the NATURAL too. Imho, this would be a great advantage for WordPress at all.

I like small solutions with uses as much standards as possible. This is more flexible for any future development and still much reliable, because there is not a lot of code, which could be buggy. That’s the main reason, why I decided to use the WordPress WP_Query solution together with the MySQL FULLTEXT index.

Indeed there are better implementations with more feature, like Better Search, Relevanssi and WPFTS offers. But I don’t need them. And that three tools didn’t support the excerpt field completely.

We’re living in a not-perfect world. And each of us has to choose a way for himself to handle this issue. ??

Right now, I tried to implement the hiding solution for the problems. While doing this, I missed a feature to hide special bugs of the WordPress core. Exactly still a problem in “wp-includes/update.php:155”.

Am I right, there is no filter which I can use to hide this bug only?

@epsiloncool: I took a look into the code of WPFTS. I wasn’t be able to find a MATCH ... AGAINST solution. But I think that solution is the best one, since MySQL has much more experience, knowledge and tester like you. Because of them, I trust that solution more, than your implementation. I’m sorry.

But right now, I understood the BOOLEAN MODE of the MATCH ... AGAINST solution: It does not offer a relevance search. So I switched to the NATURAL MODE.

There are a lot of different solutions:

• WordPress Core search with simple LIKE '%pattern%'
• Better Search with several combinded searches
• Relevanssi and WPFTS with own Indexes
• A Google plugin with their search implementation
• A simple MATCH ... AGAINST solution

Seems, there is no right solution…

So I for myself decided to use the WordPress function (WP_Query class) with a MATCH ... AGAINST extension in NATURAL MODE. For my private usage (as admin) I implemented a further checkbox for the BOOLEAN MODE. May everbody choose an other solution… ??

Nevertheless thank you for showing me the WPFTS. Unfortunatly, I didn’t found it by myself. But additional, I do not install software with so less user votings, since I don’t want to risk some demages…

Both of us learned something from the other and both of us can use it for his goals. So we both should be happy to meat another. But for me the problem is solved and I don’t think you want further assistance from me. So imho up from now we do not longer need to work together.

Thanks a lot and best wishes!

Instead of using your aggressiv mode, maybe the Query Expansion could be a solution for you: https://dev.mysql.com/doc/refman/8.0/en/fulltext-query-expansion.html. (But I didn’t read this well, so maybe I’m wrong.)

I decided not to use Better Search, I implemented my own solution. If you are interested about it, please take a look to it. Otherwise please excuse me to post it.

Thanks a lot for teaching me, how it works.

  add_filter( 'posts_fields', 'nw_posts_fields', 10, 2 );
  function nw_posts_fields( $fields, $query ) {
    global $wpdb;
    
    if ( is_search() )
      $fields .= ', MATCH (' . $wpdb->posts . '.post_title) AGAINST (\'' . $wpdb->_escape($query->query['s']) . '\') * 10'
        . ' + MATCH (' . $wpdb->posts . '.post_excerpt) AGAINST (\'' . $wpdb->_escape($query->query['s']) . '\') * 5'
        . ' + MATCH (' . $wpdb->posts . '.post_content) AGAINST (\'' . $wpdb->_escape($query->query['s']) . '\') * 1 AS score';
  
    return $fields;
  }
  add_filter( 'posts_search', 'nw_posts_search', 10, 2 );
  function nw_posts_search( $fields, $query ) {
    global $wpdb;
    
    if ( is_search() )
      return ' AND (MATCH (' . $wpdb->posts . '.post_title) AGAINST (\'' . $wpdb->_escape($query->query['s']) . '\')'
        . ' OR MATCH (' . $wpdb->posts . '.post_excerpt) AGAINST (\'' . $wpdb->_escape($query->query['s']) . '\')'
        . ' OR MATCH (' . $wpdb->posts . '.post_content) AGAINST (\'' . $wpdb->_escape($query->query['s']) . '\'))';
    else
      return $fields;
  }
  add_filter( 'posts_search_orderby', 'nw_posts_search_orderby', 10, 2 );
  function nw_posts_search_orderby( $orderby, $query ) {
    global $wpdb;
    
    if ( is_search() )
      if ( $orderby )
        $orderby = 'score DESC, ' . $orderby;
      else
        $orderby = 'score DESC';
  
    return $orderby;
  }

This gives me this query:

SELECT SQL_CALC_FOUND_ROWS wp_posts.*, MATCH (wp_posts.post_title) AGAINST ('gott') * 10 + MATCH (wp_posts.post_excerpt) AGAINST ('gott') * 5 + MATCH (wp_posts.post_content) AGAINST ('gott') * 1 AS score
FROM wp_posts
LEFT JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.object_id)
WHERE 1=1
AND ( wp_term_relationships.term_taxonomy_id IN (5,6,9) )
AND (MATCH (wp_posts.post_title) AGAINST ('gott')
OR MATCH (wp_posts.post_excerpt) AGAINST ('gott')
OR MATCH (wp_posts.post_content) AGAINST ('gott'))
AND wp_posts.post_type IN ('post', 'page', 'attachment')
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_author = 1
AND wp_posts.post_status = 'private')
GROUP BY wp_posts.ID
ORDER BY score DESC, wp_posts.post_title LIKE '%gott%' DESC, wp_posts.post_date DESC
LIMIT 0, 10

The category restriction is realized with this:

  add_action( 'pre_get_posts', 'nw_pre_get_posts' );
  function nw_pre_get_posts( $query ) {
    if ( $query->is_search() )
      $query->set( 'cat', 5, 6, 9 );
  }

… maybe too simple, to use a pugin for this… ??

• This reply was modified 5 years, 5 months ago by tahtu.
• This reply was modified 5 years, 5 months ago by tahtu.

Detecting ourself and finding out our own limitations are the greates problems for us humans. If you don’t follow what I told to you … maybe it’s a problem from you. But you don’t think about that. ??

You have a lot of great knowledge. But it’s not easy to me to find them by myself. Maybe you could think about, how you could offer this knowledge more easily to people like me? …

Thank’s alot for this filter!

Im sorry, but I don’t really understand your question about the Seamless mode. Maybe my english is too bad to understand you. As far as I understood your question, you want to know how you can offer your filters further more, if you use the suggested WP_Query solution. Did I understood you well?

If you use the suggested WP_Query filters to implement Better Search … the webmaster (users of Better Search) can do the same. They can hook on the same filters with a higher or lower priority. So they can decide, to hook after Better Search. Than they can modify the SQL code, after you did it too.

Imho, this way would be the best. But in this case, all the webmaster, who still uses your filters has to modify his codes, since they have to hook on different filters up from now. Indeed you could also trow out you (old) filter inside the filters you hooked in. But in this case you would break the idea of the WordPress filters completely. I suggest not to do this.

Well, this problem (of changing the filter by the webmasters) occurs, since you didn’t use the standards of WordPress until now. This exactly is the reason, why you should allways!!! use existing standards. ??

About your Aggressiv search: I believe it’s the same problem, like you current implementation with your own SQL creation: You try to be better than other developer (people). This is a normal way from the most human people: They think, they would be better than other ones – but often this based on wrong thinking. Like I tried to tell you, your own SQL creation and offering filters inside of it is bad. Also from my point of view the whole Aggressiv Search is bad. Imho, you should remove it completely.

By using the MATCH ... AGAINST clause, you are using a feature of the MySQL server. You should not think, you would be able to do this better than the developer of MySQL. Yes, indeed you think you could everything better than other developer, but like mentioned, you can’t. So you should not try this, but you should trust the MySQL developers to do their job as well. And if the MATCH ... AGAINST search does not offer a result, please just accept this.

Relevanssi have also problems of this arrogant thinking: The believe to be able to implement a better index than the MySQL database can do this. But the wildcard search (for example search about “Relev*” (with a “*”) does not results posts with “Relevanssi”. But the most Internet users know this wildcard search patter from Google. So, it should be work inside the search on WordPress too.

Using MATCH ... AGAINST offers this wildcard solution – and more features. Because of this, I think this implementation is the best one which can be used together with WordPress. But the disvantage of it is: The large fulltext index inside the database. On large sites with very many posts inside of it, can get trouble with this. Maybe that’s the reason, why WordPress didn’t implement that solution.

Indeed developers like you tries to implement a lot of features. But you risk to overload your software with that. And than webmaster are not satisfied with it and don’t use it.

Because of this, I looking for a small solution with FULLTEXT index and MATCH ... AGAINST, ordered by relavance. But nothing more. This forces to create the indices and modifing the three mentioned WP_Query filters. That’s all. That improves the WordPress search very much. But still offers the great implementations of standard pre_get_posts manipulation for the webmasters, which is documented as well and works without Better Search as well too. Or with other words: If a webmaster still uses the pre_get_posts hook, it works as well further more after installing Better Search. So Better Search is much more compatible to WordPress with that solution … and the webmasters are happy about it. This should be your destination: Making the webmaster happy – not making yourself happy. (That is selfish, not loveful ?? )

Btw: I found an other problem of your SQL creation: It always search for publish posts. Also, for logged in admins. But they want to find draft posts too. Indeed you could implement a solution for this too. But why should you do this, if WordPress offers this feature already? This is wasting time because of selfish thinking…

Please sorry about my “selfish” blaming. I respect you and your work. I didn’t know the MATCH ... AGAINST feature of the MySQL database before. Addition, I learned a lot of executing SQL code inside WordPress too. So I’m really happy about your work. You teached me a lot of great knowledge, which helped me to expand my knowledge.

• This reply was modified 5 years, 5 months ago by tahtu.

I spend more time about my search implementation – and I removed Relevanssi, since it’s much overloaded and takes too many resources.

Since I still think using the WP_Query class is the best solution, I’ve took a look to it again. Additional, I compared the SQL query of Better Search and the original WP_Query implementation.

While doing this, I recognized, you have to change something inside the WHERE clause too. You could implement that changes by using the posts_search filter. So I still think, you could replace a lot of your code by using the WP_Query class.

… and I decided to use Better Search, if you would do that. Other wise I would do this by my self, since I believe it’s the best solution not to implement too much unneeded code. So now, I will wait if you realize my suggestions before I implement my own solution.

I hope you don’t feel bothered with my many posts in this forum? This was my last one, if you don’t ask me something further more.