Saturday, January 22, 2005

suggest performance issue

Thanks to John, I found out today, that my WordPress plug-in 'suggest', performs very slow with large databases. At least I think it's because of the very generic sql query I use. To read the post contents off the database I use this query: select distinct post_content as val from wp_content where post_content like '%ja%' order by val. Okay, I admit the order by is not necessary, I'm gonna eliminate it with the next version.

But what can I do to tweak the performance? I don't think there are a lot of possibilities. I thought of a configuration option, for blogs with a lot of content, to search only the title of the posts. It's an option, but not a great solution!

After I retrieved the content of the posts, matching the like statement , I run two foreach loops and execute a preg_match and a preg_match_all in almost every step. I think I could handle the parsing of the content with one preg_match, but I definitely need the two loops! Mhh...I'm not too sure if there is something to tweak!

At the end of the code I execute another database query to get the number of the posts, of the parsed post content (the suggestions): select distinct count(*) as cnt from $tableposts where post_content like '%java%'. I could provide an option to disable the result search, together with the already exisiting option "don't display results". The cost of one database query and one loop should reduce the execution time.

Maybe someone could help me doing a code review and find possible tweaks to improve performance with large databases.

No comments: