Sitecore fast query not returning items when searching for item names issue and how to fix

 I've recently hit a snag while using Sitecore's fast query. To begin with, I first noticed the issue when one of my colleagues reported that the upload application we have recently developed is not working. The upload application is supposed to add children to items that already existed. It turns out that the issue was the fast query I have written was not finding the parent item those children are supposed to be loaded under.

 Here's a look of the query I was using:

fast:/sitecore/content/home/products//X7500

I know this parent item exists as I can view it in the content editor.

 After a few tries using the XPath Builder, Sitecore Rocks Query Analyzer and its own XPath builder, I have concluded that this might be something I can spend hours spinning my wheels but not be able to solve. So I turned to Sitecore support. After a day of corresponding in the Sitecore support portal, the engineer came back and told me that I need to run the Clean Database interface and so I did.

Now, this Clean Database interface is one of those that are not well documented (or I might not have done my home work or research well enough). It turns out that when you run this database clean up, the time it will take depends on the size of your master database. In my case, it was a little under an hour. Here's another interesting thought, it kept on timing out on my first couple of tries. Now since this is a background process, I decided to close the modal window after I have  started the process and tail my logs to see what's happening. Sitecore logs stated that the Database Clean up has started and after a few minutes it stated that the job completed (no error message though) so I thought everything went well.

 When I tried the same fast query above, it still didn't work. So I did some research and find out that the DefaultSqlTimeout setting in the web.config is initially set to 5 minutes by default. After adjusting that value I re-run the Clean Database interface and let the modal window stay and this time it completed with no issues and my fast query is now returning the parent items which were not being found earlier.

One thing I recall about those parent items that were not being found, we actually move that item from a staging website to the production website in the same Sitecore installation. I'm not certain if that was the root cause but it might have contributed to it.