Port issue views to Search API so we can have a performant backend

@DamZ, based upon your https://drupal.org/node/1699164#comment-6354532 comment, what is the approximate level of effort needed to implement something like this?

I took this for a spin, so as to figure out a precise task list.

The good news is that it was relatively easy to put a prototype together. The bad news is that not everything magically worked :)

Here is the task list (I tagged the open issues with the "Search API on Drupal.org" tag):

• #1832348: Add a computed property to load the comments of a node
• #1832356: Figure out how to dereference option lists with Search API
• #1832360: Implement a Views argument for entities
• Figure out how to implement autocomplete for Assigned, Submitted by, Participant and Issue Tags (I suppose we could implement a generic solution for this in Entity Reference, except that Assigned "Submitted by" itself is not an Entity Reference field...)
• Discuss if it's acceptable not to be able to sort by title
• Discuss if we should transform the advanced search for into a facetted form

I just pushed the prototype to the 949372-search-api branch.

@dww: that was a brain fart. I meant "Submitted by" is not an entity reference field.

There is already an issue for the comments proerty: #1414688: Add the comments property in entity_metadata_node_entity_property_info(). I tagged it accordingly and marked yours as a duplicate. It seems like fago is fine with committing it, it just needs a quick polish.

Figure out how to implement autocomplete for Assigned, Submitted by, Participant and Issue Tags (I suppose we could implement a generic solution for this in Entity Reference, except that Assigned "Submitted by" itself is not an Entity Reference field...)

Would this really deal with the form generated by Views? Or do you mean overriding the Views integration in the Entity Reference module?
In any case, a custom override will probably be necessary, at least for the “Submitted by” field. And probably will be rather simple to do.

Discuss if it's acceptable not to be able to sort by title

Otherwise we can always add a aggregated field for indexing the title as fulltext, and index the title itself as a string. (In case you didn't know that.)
In principle, I'd say sorting by title is no really useful feature, or only in rare cases. However, those cases exist, I guess – and also the table would look a bit off, with only one column not sortable.
(Also, when sorting by „Assigned to” remember that this will normally sort by the users' UIDs, not their names! If you want the probably expected behavior of sorting by name, the name is what you need to index, not the “Assigned to” field itself.)

Discuss if we should transform the advanced search for into a facetted form

Adding facets in one way or another would definitely be a huge plus! I can't really say if the impact on the performance would be acceptable, but I'd say that having facets in lieu of nearly all exposed form elements (except “Search” and “Status”, I'd say) would even be a big win for the normal search.
I'm not a UX expert in any way (as you're using the Search API, you're bound to have noticed), but that's what I'd rather use, I think.

What are the benefits of Search API vs apachesolr_views?

So I guess that's the issue -- we just need to make sure there's a way to filter by node author. Seems like that's *got* to be handled by Search API already, no?

Of course that's handled – it probably just lacks autocompletion. And you have to remember to index and use “Author » Name”, not “Author”.

- Sorting by title: I'm not too concerned about this. AFAICT, the only useful *sorts* for issue views are: 'last commented on' (default), 'priority', 'created', # of replies, and maybe version and category. Generally I'd say that category, version and assigned are much more useful as a filter. If it's a pain in the ass to be able to sort by title, I'm fine with dropping that (although I must admit I don't understand Search API well enough to know why this doesn't already work).

Since fulltext fields are parsed and tokenized, we don't have the single string we'd need in the index to sort by the field. Therefore, you can have a single field either fulltext-searchable or sortable, not both. You'd have to copy the field with the “Aggregated fields“ data alteration to do that.

Anyways, I totally agree with you, most of the sorts aren't very useful, so we could just drop some. However, being able to sort by title isn't really a “pain in the ass”, just a few clicks and a bit of extra space consumption for the index.

While we're talking about implementation details, the biggest thing I'm concerned about would be how to get this to play nicely with the "Follow" flag as described at #1828756: D7 views don't honor tracker and issue following. I think we're going to need a field in the Search API index for each issue with the uids of everyone following that issue, so that both the Your issues view and the "participant" filter on advanced search work (although that's a bit confusing, and we should probably keep a separate filter for "participant" vs. "following" or something). That'd also mean we'd need to reindex the issue for Search API every time a user (un)flags that issue. Ideally with little or no latency. ;) I'd rather discuss this at the other issue, but I wanted to raise it here to get it in everyone's mind.

Ah, yes, that's an interesting issue … Including that data into the index wouldn't be much work, I guess. However, having to reindex the whole issue when a user (un)flags it would really be unfortunate. I can't see how we could avoid that, though. I can't really tell how much additional strain this would put on the server, though. Probably there aren't a lot of people subscribing to new issues, compared to the whole rest – but I might be mistaken there.

I think it would be an very bad architecture decision to use Search API in addition to Apache Solr.

Just to add on to this discussion, you should figure out if solr is even an option for this problem. Keep in mind that Solr (< 4) has a waiting time before it processes the items. If you create an issue, it will not show up in the issue list because it will need to wait before it is being sent to solr and then you need to wait before it actually processes it. This latency can get pretty high.

Imagine : A user creates an issue

wait for cron to pass by : 5 min (optimistic)
wait for solr to process : 2 min
wait for solr to replicate to the slave : 1 min

You could avoid the cron by adding it to the index directly when you do a node save, but I'm still seeing some thing being shoehorned in to place where it does not belong.

Please think this through before making hasty decisions. I also agree with pwolanin that adding Search API now, just for this functionality & with the arguments about solr I added above, is not the right way to go.

If table joins are an issue, maybe look at http://drupal.org/project/pbs instead of materialized Views?

@dww I wrote in minutes, you clearly ignored my statement of the solr replication and process time?

There is no way that in Solr < 4 you can tune solr to directly return you the committed docs. Not without completely breaking it. If you will rely on solr for this listing, I think we can expect that a new issue is seen almost directly and that will never be the case with solr.
I've seen many people try, and it's just not feasible right now. Unless drupal.org wants to make investments in upgrading to solr 4.0. But given the timeframe, that is also not a workable option.

I have to agree strongly with Nick here - the indexing and replication lag makes Solr unsuitable for listing pages where uses expect to see immediate updates.

Solr 3.x should be able to handle a ~10s auto commit time, without a lot of issue (especially on our hardware). The near realtime feature of Solr 4.x is compelling to reduce the latency further, and I don't see why we couldn't update the cluster to this version.

http://wiki.apache.org/solr/SolrPerformanceFactors#Updates_and_Commit_Fr...

I'll try to explain why what you are saying is not plausible in a solr master-slave setup. And I assume drupal.org does have a master-slave setup.
A quote from this wiki

If you desire frequent new collections in order for your most recent changes to appear "live online", you must have both frequent commits/snapshots and frequent snappulls. The most frequently you can distribute index changes and maintain good performance is probably in the range of 1 to 5 minutes, depending on your reliance on caching for good query times, and the time it takes to autowarm those caches.

1 to 5 minutes, nothing even below that is recommended.

There is significant work in upgrading both solr modules to solr 4.0 and as I said before, then we need to prepare for this work and this won't be that easy given the timeframe and available human forces working on this.

@Damien Tournoud - Solr 4 needs a revised set of config files. We have never deployed them in production yet. Have you?

The NRT feature does NOT come free. I don't think you want to delay this d.o upgrade by figuring out how to run SolrCloud or otherwise doing distributed updates rather than master-slave replication.

Note also - the current d.o setup has Varnish in front of Solr, and I expect the TTL is in the 100's of seconds?

Again, I think this is not the right tool for the job in this case.

All this confirms that Search API is the good solution for this. If Solr as a backend doesn't work in this particular scenario, we can easily switch to another backend (ElasticSearch and Sphinx come immediately to mind).

Here is the major problem query off the main issue listing (/project/issues): http://drupal.privatepaste.com/208f1d6a0a

I have not review it in detail, but this + the COUNT() is taking 8+ seconds.

-N

This final part of the where looks rather odd:

(node.status = 1 OR (node.uid = 94675 AND 94675 <> 0 AND 1 = 1) OR 1 = 1)

So, is it just the one single view for the issue listings that's the issue?

Thanks for your input, Nick! It seems Solr is really not an option then, unless you want to loosen the realtime criterion …

Regarding MV, I can't really say I know much about this module, but what I've just read doesn't sound like it would fit into the Search API model very neatly. Since it defines both the data and how it is queried, in Search API terms I think it would have to be both an item type and a service class? Or just a service class, where the index settings determine what the materialized views look like – but that would be practically no different from the Database search backend. We'd probably also have to add fulltext searching capabilities on our own, since I wouldn't think MV supports that currently (other than through the DBMS's native method).
Also, it seems the module hasn't had a commit in more than a year and hasn't got more than two installs, both of which also aren't very good signs …

Using the normal Database search backend for now might really be the best option – I usually can't recommend this at all to larger sites, but it ought to be better than the normal Views queries.
And, as you say, using the Search API gives you at least the flexibility to change this at any point should something better come up.

In the weekly solr - drupal.org meeting we still were not convinced there is a solution that is able to be deployed on drupal.org in a short-term. I started looking at mysql views and materialized views.

The code below is the complete views query without filters and others. It is not a full materialized view but we could make it that way if we want, a full materialized view would be more performant but it requires a trigger in the node table. This trigger would update the entry in the materialized view and this way we would have our denormalized table in mysql with real time updates.

There is *no* need for a drupal module to achieve this functionality, just some mariadb/mysql mastery. The only drupal adjustment we'd have to make then is to expose this table to drupal views and recreate the view.

On another note, I'd advise against materialized views as a module as this would add more overhead to a site that does not want more modules. Also we know not a lot about it, and lack the expertise I think? I'd be happy to be proven otherwise.

In the meantime, if you want to start playing with this and/or make the code to expose the table to the view :

CREATE OR REPLACE VIEW issue_queue_nodes_cache AS 
SELECT node.title AS node_title, node.nid AS nid, history.timestamp AS history_timestamp, node.created AS node_created, node.changed AS node_changed, node_comment_statistics.comment_count AS node_comment_statistics_comment_count, node.type AS node_type, node_comment_statistics.last_comment_timestamp AS node_comment_statistics_last_comment_timestamp, users_field_data_field_issue_assigned.name AS users_field_data_field_issue_assigned_name, users_field_data_field_issue_assigned.uid AS users_field_data_field_issue_assigned_uid, 'node' AS field_data_field_issue_status_node_entity_type, 'node' AS field_data_field_issue_priority_node_entity_type, 'node' AS field_data_field_issue_category_node_entity_type, 'node' AS field_data_field_issue_version_node_entity_type, 'node' AS field_data_field_issue_component_node_entity_type
FROM 
node node
LEFT JOIN field_data_field_issue_assigned field_data_field_issue_assigned ON node.nid = field_data_field_issue_assigned.entity_id AND (field_data_field_issue_assigned.entity_type = 'node' AND field_data_field_issue_assigned.deleted = '0')
LEFT JOIN users users_field_data_field_issue_assigned ON field_data_field_issue_assigned.field_issue_assigned_target_id = users_field_data_field_issue_assigned.uid
LEFT JOIN field_data_field_project field_data_field_project ON node.nid = field_data_field_project.entity_id AND (field_data_field_project.entity_type = 'node' AND field_data_field_project.deleted = '0')
INNER JOIN node node_field_data_field_project ON field_data_field_project.field_project_target_id = node_field_data_field_project.nid
INNER JOIN field_data_field_issue_status field_data_field_issue_status ON node.nid = field_data_field_issue_status.entity_id AND (field_data_field_issue_status.entity_type = 'node' AND field_data_field_issue_status.deleted = '0')
LEFT JOIN history history ON node.nid = history.nid AND history.uid = '1'
INNER JOIN node_comment_statistics node_comment_statistics ON node.nid = node_comment_statistics.nid
ORDER BY node_comment_statistics_last_comment_timestamp DESC

Some links : http://www.fromdual.com/mysql-materialized-views

The thing I find appealing about getting MV module to work is that if it can be made to work for Drupal.org, it can likely also be made to work for any other D7 site out there hitting performance issues with Views. This helps justify the investment in the D7 upgrade by bringing real, tangible benefits 99% of our userbase, where custom SQL triggers and custom searching infrastructure only benefit ourselves and are not easily reproducible in other environments that do not have a Narayan. ;) They also make deployments trickier (or at least Solr has been blamed for staving off at least 3 d.o features I've been involved with, and it's been really frustrating).

I pointed this out on IRC and was told that the query was more complicated than merely MV being able to help with, but it's not clear to me exactly how/why that is. Would be great to get an issue summary that reflects the current state of things.

Changing the topic title, will tackle the summary also

Adding several sections, and a blocker: This issue is being blocked by #949374: Add a project_issue_solr module to put issue metadata into the solr index

Let's not forget that we need a fulltext search. The main view is perfectly trivial to denormalize. This issue is about rebuilding the Project Issue architecture using Search API so that we can leverage external fulltext search engines instead of trying to do this in SQL.

Then let's confirm the full-text search in sql is the issue. FWIW, this has been working in Drupal 6 and I don't see major changes in D7 in the functional requirements.
My understanding is that the join causes the high mysql load as pointed out in and this materialised view could solve this. And don't get me wrong, I don't care how it is being solved, but the solution should be a workable option for everyone.
I just don't see how adding Search API to this equation would solve the issue? Or is someone committed to work on the materialised views backend for search api? That would be awesome.

Just keep in mind that we are ending the development period for drupal.org on D7 and that we should be careful in adding new layers. Perhaps we should do some more profiling to find and make the issue reproducible, so that we can properly test and benchmark the work that is being done.

@Nick_vh: it has always been an issue. Those are part of the slowest queries of Drupal.org.

The pages at http://drupal.org/project/issues/search?text=[any popular keyword] usually just timeout. This whole view is disabled to anonymous users because it is so slow.

Let's stop using SQL as a fulltext search engine, this is what this issue is about.

ElasticSearch seems to be a good match for all our requirements (including the realtime aspect). There is a Elasticsearch connector for Search API already (actually two: 1 and 2), so we have something to start working from.

We had good success leveraging ElasticSearch in production in the past (on non-Drupal projects). ElasticSearch is in particular the indexation engine behind the Graylog2 log management solution. The setup (on a two server cluster with one replica) should be trivial on Drupal.org.

@webchick:

They also make deployments trickier (or at least Solr has been blamed for staving off at least 3 d.o features I've been involved with, and it's been really frustrating).

This has been the case because we built a lot of custom layers on top of the relatively feature-poor Apache Solr module. One of those features is the project browsing that we implemented in the project_solr module with dww back when we ported Drupal.org to Drupal 6. This module can (and should) die. It can be replaced by a Search API index and a standard view very very easily.

Rebuilding those features on top of the standard Search API would allow us to be more agile and more flexible. This is in part what this issue is about. I'm not sure what all the push-back is about.

Even though I dig elasticsearch, I'm not sure if it is a solution Drupal.org wants to maintain next to a dozen of mysql and solr servers. And the investment is just as much as moving to Solr 4.0. Perhaps drupal.org can also wait till the solr modules are stable on Solr 4.0 as it would offer the same solution and there would be better community support (regardlessly of which backend, apachesolr or search api, you use) imho.

I'll leave it to the drupal.org crew to decide what they want to do here.

Changing summary

For full text I have another suggestion:

* I have worked on a prototype after Munich to replace the core search module with a Solr Query.

That is actually quite easy as Views is just loading the nids anyway, so all we need to do is filter:

SELECT MV QUERY ... WHERE nid IN (list-of-nids-from-solr) AND ...

The only problematic parts are:

* Exposed filters need to be translated into Solr queries
* Paging needs to be done via Solr

Besides that core search is doing nothing else than to restrict the result-set of the MySQL query, so with some constraints it is a viable solution.

Yes, it will take a moment for new content to show up, but for full text search that should not be a problem ... (and core search is dependent on cron too).

@FabianX: are you really suggesting doing the intersection of two queries like this? This will fail in 99% of the use cases. The way to make this work is to guarantee that the list fetched from Solr is bounded, which means doing the paging in Solr, which means doing the ordering in Solr too. So this will only work when doing full text queries *and* sorting by relevancy.

Anyway, this is just too much work when we can just rely on Search API and be done with it :)

Just want to clarify, where is the requirement for Solr 4 coming from? Is it just the speed of the index update?

@dww Thanks for the summary of where we are.

I've just had a quick look at the code for the Search API DB backend, and it seems to index all fields into different tables, so isn't denormalised at all? The 'quick win' Search API DB backend might be a non-starter too.

I'm a huge proponent of something like #1836928: Materialized Views Search API backend which makes the solution to this problem something sites outside of Drupal.org can benefit from. That's a sure-fire way to both demonstrate "return on investment" for the Drupal.org upgrade, as well as help to encourage people other than the Drupal Association to work on further improvements.

How about instead of #1836928: Materialized Views Search API backend we improve the Search API Database Search module to be able to put as much in a single table as possible, given the usage statistics of the two modules, that might benefit the community more? It might also be significantly easier since Search API Database Search already does full-text search, whereas Materialised views does not.

If such an approach was desired, then I think the following could happen:

1. Continue with the Search API port that Damien started in the 949372-search-api branch.
2. Continue with the Database Search backend, but try to improve it so that as much of the data as possible gets denormalised (PBS has some nice code that might be useful here)
3. (In the future) When the Search API Solr backend, and apachesolr modules can talk to Solr 4, and the d.o infra has it, move to Solr 4, and the low latency goodness that that brings..

Removing the solr tag since solr isn't a likely solution, and if it is, it is different from the solr elsewhere on Drupal.org D7.

Looking at http://drupal.org/project/search_api_db, I'm not seeing that it is suited fully for our needs. It appears to keep each field in a separate table. What we need is a a big, denormalized table so we can generate issue queue listings with more simple queries that can be executed well enough by SQL, that's Materialized Views.

The way forward is still what dww said in #38:

- Continue with the [Project Issue] Search API port that Damien started in the 949372-search-api branch.
- Continue with the Database Search backend as I mentioned at #3 and drunken monkey supported in #26 in response to #1836928: Materialized Views Search API backend.
- [Do a proof of concept and] get nnewton's input on if he'd like to continue with database search or investigate some other possible search backends.

Looking at http://drupal.org/project/search_api_db, I'm not seeing that it is suited fully for our needs. It appears to keep each field in a separate table. What we need is a a big, denormalized table so we can generate issue queue listings with more simple queries that can be executed well enough by SQL, that's Materialized Views.

As Steven said, we'd need to improve/adapt it so it is also capable of storing as much as possible in a single table. Which could be less work than creating a MV Search API backend from scratch.
The main problem I see with using a single table for the whole index are multi-valued and fulltext fields. When using a single table, we'd practically need to have one row per field value (or, for fulltext fields, one row per distinct word in it). That, of course, would be insane. So, we'd need a plan to deal with this. I'm coming up with the following options here:

• Store only all single-valued, non-fulltext fields in the combined table, and keep the separate tables for multi-valued and fulltext fields. I'd imagine this would be an improvement for most sites, so we could just add this as a normal patch to the DB search module.
• Possible solution for fulltext fields: Don't index them in a special way, and use LIKE (or, optionally, vendor-specific methods) at search time to search it. This would also allow the backend to correctly handle phrase searches, which currently aren't supported by the DB backend.
• Possible solution for multi-valued fields: Maybe we could concatenate all values of those with a special separator, so we wouldn't need the additional table. Probably too detrimental to performance to be generally used, but maybe still the right thing for drupal.org? Could be tested, I guess.

Apart from the first one, these probably couldn't be just added to the Search API Database Search module. We'd either have to add an option for servers, letting users decide which way they'd like their fulltext/multi-valued fields handled; or fork the module (either into a new project or a 2.x branch) and hard-code the other mechanism, explaining this in the project description. (The latter would also allow us to specify that some features are not supported with that service backend, should we find we don't need them on drupal.org and they'd make it more difficult to implement.) The first would of course make the code more bloated and complicated (since code in the service class would then have to deal with two different data structures), the second would be harder to maintain (having to commit patches unrelated to the differing part to both projects).

That said, I don't really know much about Materialized Views, so it could easily be the better option here. The above are just my thoughts if you decided to go with the existing DB backend.

Before we dive in the very complex matter of creating a new module, did someone ever test materialized views with the current problem set?

I really don't want to open up can of worms again, but what exactly were the reasons for not using Solr as the backend?

Latency (or speed of updates)? Infrastructure change/setup?

Re #45, search_api_db improvements:

Drupal.org doesn't really have multi-value fields for issues, so it is a moot point. The best thing to do for search_api_db would be to leave the existing functionality of one field per table. That is probably best for sites that do have multi-value fields, a JOIN seems nicer than a LIKE, etc; they can do their own improvements if they need them.

For full text, I think it is worth considering using MySQL-specific full text. That would certainly be better than LIKE. I'm guessing the existing word-by-word scoring in search_api_db is nicer than LIKE, especially when we get into multiple words. Unless I'm mistaken, core search style word-by-word scoring is what we are doing today, so that's the baseline to meet.

The end goal is to have scalable queries. Less tables and less JOINs are great for that. We don't need to get down to one table. In the past, we used Materialized Views to denormalize the tables, we don't necessarily have to use it again, but some code has to do it. Either way, we're going to be improving one of the modules.

Drupal.org doesn't really have multi-value fields for issues, so it is a moot point.

It has tags and participants, right? So while the majority of properties is luckily single-valued, we would have to solve the multi-valued problem. But if you're also of the opinion that it needn't necessarily be only a single table, then I guess we're good to go.
For fulltext, we could really just add a server option for letting users decide whether they want to use our old method (you're right, it works similar to core search – although they use two queries, I think) or a vendor-specific method of searching a single field (LIKE for systems we don't know or which have no fulltext searching capabilities).

You can (and probably should) of course also evaluate MV as an option, but I think it wouldn't be too much effort with search_api_db, and I'd sure be willing to help with that. Currently, I'd even have the time.

a) this is no longer blocked on the (won't fixed) proposal to add more custom project* solr crap to manually add issue metadata to the solr index since we're probably not going to use solr at all. b) the PM folks have changed their minds on how to track hours estimates -- that's now handled via issue tags, not blurbs in the issue summary like this.

I updated the issue summary a bit for how to proceed with Search API. It integrates well with Views and gets the boilerplate code, which we would need for updating Materialized Views, out of the way. And I think there is good community & maintainer momentum around the Search API modules.

I clicked together a test view at http://searchapi-drupal_7.redesign.devdrupal.org/project/issues/searchapi. The dev site does come with some caveats, mainly our migration is buggy, #1975866: Issue data being migrated to D7 out of order, and #1982520: Need to migrate priority to new int values hadn't made it into the DB snapshot this dev site was built with. It does have data we can work with. More people can hack on it, see http://drupal.org/node/1018084.

The default page's query is currently

SELECT t.item_id AS item_id, '1' AS score FROM search_api_db_default_node_index_search_api_language t INNER JOIN search_api_db_default_node_index_status t_2 ON t.item_id = t_2.item_id INNER JOIN search_api_db_default_node_index_field_issue_status t_3 ON t.item_id = t_3.item_id INNER JOIN search_api_db_default_node_index_changed t_4 ON t.item_id = t_4.item_id WHERE (( (t_2.value = '1') AND( (t_3.value = '1') OR (t_3.value = '13') OR (t_3.value = '8') OR (t_3.value = '14') OR (t_3.value = '15') OR (t_3.value = '2') OR (t_3.value = '4') OR (t_3.value = '16') OR (t_3.value = '0') OR (t_3.value = '0') OR (t_3.value = '0') OR (t_3.value = '0') OR (t_3.value = '0') ))) ORDER BY t_4.value DESC LIMIT 50 OFFSET 0

which is 3 JOINs, a temporary table, and a filesort. Consolidating single-valued fields into one table will help a lot.

The rest of the rendering is fetching data from the field cache, which will be backed by MemCache in production.

Oh and I ran into #1984694: Increase weight #delta along with the number of grouped filters along the way. That's not a blocker, just annoying when manually configuring Views.

Updating with solution

Since the issue summary just got updated which made me look at the paragraph about Solr: Solr 4 is now supported (by both Search API Solr and Apachesolr Search Integration) – see #1676224: Support Apache Solr 4.0. I don't know to what extent this changes things (i.e., makes Solr an eligible option), though, or if re-evaluating this decision is even still possible at this time. Just wanted to note that.

For Solr 4, #18 makes it sound like we could get a Solr 4 index updated in near realtime. We could consider it.

I'd like to avoid a full reindex after upgrading, reducing our search downtime. We already have to reindex all projects to get the data moved to fields. Issue indexing will have to happen with any solution. For search_api_db, we could shortcut it with a hacky INSERT SELECT and then proper indexing to come back and get the full text index.

If we do go with Solr for issues, I like the idea of using Search API to keep a separate index from the main site index. I don't want to upgrade our main search code again in the Drupal.org D7 project. Solr 3 and 4 will have to coexist on our Solr server.

I'm still leaning toward search_api_db, but search_api_solr is now an option.

I have to admit, I haven't really worked with Solr 4 on a production site yet (let alone one as large as d.o's issue queues), so I can't say what obstacles you might run into with it. However, my first impulse is generally to use Solr, since most queries just perform way better with it than with the database. On the other hand, if we mostly have just filter queries (which I think is the case for the issue queues) and don't use facets, this might be the other way round.
Also, your point with re-indexing is of course valid, you couldn't really circumvent this with Solr. You could perhaps do incremental updates as well (leaving out the fulltext at first), but I don't know how much time that would save on the initial run.
Using the database first, letting a Solr server index the data parallel to that and switching the index to the Solr server later should be easily possible with the Search API, but would probably be too much additional effort for you/us.

So, I don't know how you were planning on deciding this, but I can see how the database might still be the better option here.

I posted an initial pass at the necessary changes to Search API DB in #2083079: Store single-valued fields in a single, denormalized table.

Moving this to drupalorg since this will be a bit Drupal.org-specific initially. For example, issue tags are not in project module. We may be able to get by with altering the Search API and Views configuration in Drupalorg, but that will be a bit of a pain to manage.

Do we want project issue to depend on Search API? With Search API DB, installation and indexing could mostly just work, but it is another dependency to install and inevitably will need some amount of attention from site admins. Alternatively, Views of core nodes and fields will tend to work more simply. I think from the perspective of others running project issue, they should get regular, basic views.

I don't want to attempt any sort of magic that would convert a basic View to a Search API view. I'm sure it could be done, and would get us back down to one set of views to maintain, and then they get converted. That leaves us with two sets of views to maintain, unless we require Search API.

It took me a few minutes to wrap my head around Project*/drupalorg's views altering for #1807726: Port project index view to D7. While good & extensible, I had a moment of wondering why we don't just export the view we need for our site, shortcutting moving bits of the export into 3 places.

At this point, this issue feels like something we are trying out on Drupal.org and is indeed site specific. I expect this first draft might be a bit rough. If there is a need for faster views in project issue itself, the second round of work using what we know will be a whole lot better.

#2083079: Store single-valued fields in a single, denormalized table is now passing its simpletests. More testing in general would be great.

The next steps for this issue are setting up the index and views to replace project_issue's views. And then filling in the missing pieces from the issue summary.

I pushed a few commits for support in project_issue:

• http://drupalcode.org/project/project_issue.git/commit/69cb57f adds classes for status-based coloring for SearchAPI-based views.
• http://drupalcode.org/project/project_issue.git/commit/a95bf5c makes participants, comment authors plus the original issue author, available to SearchAPI.
• http://drupalcode.org/project/project_issue.git/commit/f40ba19 shows the "Create a new issue" and "Advanced search" links on any page view.
• http://drupalcode.org/project/project_issue.git/commit/28be3dc removes the now unused project_issue_search_index module.

Update summary to explain why Solr 3 doesn't meet the requirements

update todo

2 more commits for /project/issues:

• http://drupalcode.org/project/drupalorg.git/commit/9f4b47d
• http://drupalcode.org/project/drupalorg.git/commit/a1e5cfa

I've also updated the issue summary for completed work.

more done

2 more project_issue commits:

• http://drupalcode.org/project/project_issue.git/commit/af6adfb makes the "Create a new issue" and "Advanced search" links work correctly on project-specific issue queues.
• http://drupalcode.org/project/project_issue.git/commit/b3acae8 forces a SearchAPI index update on comment and follow, so results are current.

https://drupal.org/node/185188/commits has numerous fixes, including some mentioned in #2112839: Various issue queue view anomalies.

Remaining work:

• A big INSERT SELECT statement to quickly prepopulate the non-fulltext index.
• Queuing proper indexing to get the full text indexed.
• The red updated/new after the issue title.
• Show and default sort by the score column when full text searching.

The big INSERT SELECT statement to quickly prepopulate the non-fulltext index was added with http://drupalcode.org/project/drupalorg.git/commit/5ab11e0.

Now git7site.devdrupal.org has a complete index of non-text fields. It is fully indexing updated nodes immediately, and existing nodes oldest to newest on cron. I think this is ready for performance testing.

The red updated/new after the issue title - the first half of this is in http://drupalcode.org/project/drupalorg.git/commit/452c263. I still need to update the Views to use it.

I've gotten everything I've been able to spot so far done:

• http://drupalcode.org/project/drupalorg.git/commit/356cb05 finishes adding the new indicator.
• http://drupalcode.org/project/drupalorg.git/commit/ba4dea0 hides "Anonymous" from the assigned column.
• http://drupalcode.org/project/drupalorg.git/commit/0e521ec shows and default sorts by the Score column when searching for text.

More done!