¿Cuál es la forma más eficiente de escribir un filtro de contenido complejo?

Question 1

Hace poco, hice una pregunta bastante extensa sobre un filtro de contenido para mi sitio web. La publicación se puede encontrar aquí, no dude en leerla cuando lo desee.

Acepté la respuesta de @DampeS8N porque respondió bien y me envió en la dirección correcta.

Desafortunadamente, el resultado de dicha pregunta fue que el filtro de contenido que estoy usando es simplemente demasiado ineficiente para ejecutarlo en el contexto de mi aplicación.

Aquí está la información:

Tengo ~2000+ términos del glosario* y ~1200+ perfiles de especies**
Los títulos (entrada del glosario) o nombres científicos (perfiles de especies) de estas publicaciones conforman los términos de búsqueda para el filtro.
Me gustaría filtrar el contenido de mis perfiles de especies (preferiblemente al guardar, pero podría hacerse con un trabajo cron) para buscar los términos de búsqueda anteriores y reemplazarlos con enlaces a las entradas del glosario o perfiles de especies relevantes

* Se puede encontrar una entrada del glosario aquí.

Un ejemplo de lista de entradas del glosario puede ser, caudal fin, dorsal,
filteretc.

** Se puede encontrar un perfil de especie aquí.

Un ejemplo de lista de especies puede ser, Apistogramma panduro, A. panduro, Dario dario, D. dario, Betta sp. 'Maha Chai' etc.

Aquí está el problema:

Mi CMS es impulsado por WordPress. Esto no es específicamente relevante para esta publicación, aparte de comprender la estructura de mis perfiles de especies.
Mis perfiles de especies se componen de cierta información básica almacenada en el wp_posts tabla, luego la información adicional almacenada en el wp_postmeta mesa.
La mayor parte de la información en mis perfiles de especies se almacena en una serie de meta fieldsque son entradas en el wp_postmeta mesa. Esto se puede ver a continuación.

http://www.seriouslyfish.com/species/puntius-sahyadriensis/

wp_postmeta

INSERT INTO `wp_postmeta` (`meta_id`, `post_id`, `meta_key`, `meta_value`) VALUES
(104395, 2288, 'genus', '<em>Puntius</em>'),
(104396, 2288, 'species', '<em>sahyadriensis</em>'),
(104397, 2288, 'family', 'Cyprinidae'),
(104398, 2288, 'common_names', ''),
(104399, 2288, 'distribution', '<a class="link_glossary" href="https://stackoverflow.com/glossary/e/endemic" rel="/glossary/e/endemic?hover=true">Endemic</a> to streams of the Yenna <a class="link_glossary" href="http://stackoverflow.com/glossary/r/river%20basin" rel="/glossary/r/river%20basin?hover=true">river basin</a> close to the city of Mahabaleshwar in the Western Ghats mountain range, Satara district, Maharashtra state, India.'),
(104400, 2288, 'habitat', 'The <a class="link_glossary" href="http://stackoverflow.com/glossary/r/river" rel="/glossary/r/river?hover=true">river</a> Yenna flows through lush evergreen forest meaning the hill streams in which the fish can be found are likely to be shaded by the forest canopy and dense <a class="link_glossary" href="http://stackoverflow.com/glossary/m/marginal" rel="/glossary/m/marginal?hover=true">marginal</a> vegetation. Substrates should be composed of boulders, smaller stones, <a class="link_glossary" href="http://stackoverflow.com/glossary/s/sand" rel="/glossary/s/sand?hover=true">sand</a> or <a class="link_glossary" href="http://stackoverflow.com/glossary/g/gravel" rel="/glossary/g/gravel?hover=true">gravel</a> with submerged tree roots around the margins and quieter areas in which fallen branches and leaf litter collect. As with similar members of the <a class="link_glossary" href="http://stackoverflow.com/glossary/g/genus" rel="/glossary/g/genus?hover=true">genus</a> it is likely to congregate in <a class="link_glossary" href="http://stackoverflow.com/glossary/b/backwater" rel="/glossary/b/backwater?hover=true">backwater</a> pools or deeper areas with lower flow.'),
(104402, 2288, 'max_size', 'Around 2.75"/7cm.'),
(104403, 2288, 'aquarium_size', 'It is an active <a class="link_glossary" href="http://stackoverflow.com/glossary/s/species" rel="/glossary/s/species?hover=true">species</a> and a <a class="link_glossary" href="http://stackoverflow.com/glossary/t/tank" rel="/glossary/t/tank?hover=true">tank</a> measuring at least 36" x 12" x 12"/90cm x 30cm x 30cm/85 litres is needed to house a group.'),
(104404, 2288, 'maintenance', 'Choice of decor is not as critical as water quality and the amount of swimming-space provided. We suggest keeping it in a roomy, well-planted <a class="link_glossary" href="http://stackoverflow.com/glossary/a/aquarium" rel="/glossary/a/aquarium?hover=true">aquarium</a> or alternatively it would look superb in a set-up designed to resemble a flowing <a class="link_glossary" href="http://stackoverflow.com/glossary/r/river" rel="/glossary/r/river?hover=true">river</a> with a <a class="link_glossary" href="http://stackoverflow.com/glossary/s/substrate" rel="/glossary/s/substrate?hover=true">substrate</a> of variably-sized rocks and <a class="link_glossary" href="http://stackoverflow.com/glossary/g/gravel" rel="/glossary/g/gravel?hover=true">gravel</a> and some large water-worn boulders. A rivertank manifold could also be constructed to provide naturalistic unidirectional flow. The <a class="link_glossary" href="http://stackoverflow.com/glossary/t/tank" rel="/glossary/t/tank?hover=true">tank</a> can be further furnished with driftwood branches and <a class="link_glossary" href="http://stackoverflow.com/glossary/a/aquatic" rel="/glossary/a/aquatic?hover=true">aquatic</a> plants for aesthetic value. While the vast majority of plant <a class="link_glossary" href="http://stackoverflow.com/glossary/s/species" rel="/glossary/s/species?hover=true">species</a> will fail to thrive in such conditions possibilities include hardy <a class="link_glossary" href="http://stackoverflow.com/glossary/s/species" rel="/glossary/s/species?hover=true">species</a> such as Java fern, <em>Bolbitis</em> or <em>Anubias</em> <a class="link_glossary" href="http://stackoverflow.com/glossary/s/species" rel="/glossary/s/species?hover=true">species</a> which can be grown attached to the decor. Like many other <a class="link_glossary" href="http://stackoverflow.com/glossary/s/species" rel="/glossary/s/species?hover=true">species</a> that hail from pristine natural environments it is intolerant to the accumulation of <a class="link_glossary" href="http://stackoverflow.com/glossary/o/organic" rel="/glossary/o/organic?hover=true">organic</a> wastes and requires spotless water at all times in order to thrive.'),
(104405, 2288, 'water_chemistry', '<strong>Temperature</strong>: Prefers slightly cool conditions within the range 20 - 24°C/68 - 75°C. Higher temperatures are known to stimulate spawning with an associated increase of aggression in males.\r\n\r\n<strong>pH</strong>: 6.8 - 7.8\r\n\r\n<strong>Hardness</strong>: 5 - 15°H'),
(104406, 2288, 'diet', 'Likely to feed on small invertebrates, <a class="link_glossary" href="http://stackoverflow.com/glossary/a/algae" rel="/glossary/a/algae?hover=true">algae</a> and other <a class="link_glossary" href="http://stackoverflow.com/glossary/z/zooplankton" rel="/glossary/z/zooplankton?hover=true">zooplankton</a> in nature. In the <a class="link_glossary" href="http://stackoverflow.com/glossary/a/aquarium" rel="/glossary/a/aquarium?hover=true">aquarium</a> it will accept dried foods of a suitable size but should not be fed these exclusively. Daily meals of small live and frozen fare such as <em><a class="link_glossary" href="http://stackoverflow.com/glossary/D/Daphnia" rel="/glossary/D/Daphnia?hover=true">Daphnia</a></em>, <em><a class="link_glossary" href="http://stackoverflow.com/glossary/A/Artemia" rel="/glossary/A/Artemia?hover=true">Artemia</a></em> and suchlike will result in the best colouration and encourage the fish to come into breeding condition.'),
(104407, 2288, 'behaviour', 'Not an aggressive fish but best kept with other hillstream-dwelling <a class="link_glossary" href="http://stackoverflow.com/glossary/s/species" rel="/glossary/s/species?hover=true">species</a> such as <em>Danio</em>, <em>Devario</em>, other small <em>Puntius</em>, <em>Garra</em> and balitorid loaches. That said provided its oxygen and temperature requirements can be met it can be mixed with most peaceful fish too large to be considered food. A <a class="link_glossary" href="http://stackoverflow.com/glossary/b/biotope" rel="/glossary/b/biotope?hover=true">biotope</a>-style <a class="link_glossary" href="http://stackoverflow.com/glossary/c/community" rel="/glossary/c/community?hover=true">community</a> based around <a class="link_glossary" href="http://stackoverflow.com/glossary/s/species" rel="/glossary/s/species?hover=true">species</a> from hill streams of the Western Ghats would also make an interesting project with <em><a class="link_species" href="http://stackoverflow.com/species/puntius-filamentosus" rel="/species/puntius-filamentosus/?hover=true">Puntius filamentosus</a></em>, <em><a class="link_species" href="http://stackoverflow.com/species/puntius-fasciatus" rel="/species/puntius-fasciatus/?hover=true">P. fasciatus</a></em>, <em><a class="link_species" href="http://stackoverflow.com/species/puntius-narayani" rel="/species/puntius-narayani/?hover=true">P. narayani</a></em>, <em><a class="link_species" href="http://stackoverflow.com/species/puntius-ticto" rel="/species/puntius-ticto/?hover=true">P. ticto</a></em>, <em>Barilius bakeri</em>, <em>B. canarensis</em>, <em><a class="link_species" href="http://stackoverflow.com/speciesario-aequipinnatus" rel="/speciesario-aequipinnatus/?hover=true">Devario aequipinnatus</a></em>, <em><a class="link_species" href="http://stackoverflow.com/speciesario-malabaricus" rel="/speciesario-malabaricus/?hover=true">D. malabaricus</a></em>, <em>Rasbora daniconius</em>, <em>Laubuca laubuca</em>, <em>Nemacheilus rupelli</em>, <em>Mesonemacheilus triangularis</em> and <em><a class="link_species" href="http://stackoverflow.com/species/mesonoemacheilus-guentheri" rel="/species/mesonoemacheilus-guentheri/?hover=true">M. guentheri</a></em> among the numerous suitable <a class="link_glossary" href="http://stackoverflow.com/glossary/s/species" rel="/glossary/s/species?hover=true">species</a> available in the trade at times.\r\n\r\nIt''s a shoaling <a class="link_glossary" href="http://stackoverflow.com/glossary/s/species" rel="/glossary/s/species?hover=true">species</a> by nature and really should be kept in a group of at least 8-10 specimens. Maintaining it in decent numbers will not only make the fish less nervous but will result in a more effective, natural-looking display while allowing individuals some respite from the vigorous alpha male(s). Males will also display their best colours and some interesting behaviour as they compete with one other for female attention. In particular the dominant individual in a given group will develop some stunning colouration.'),
(104408, 2288, 'dimorphism', 'The male is noticeably slimmer and more brightly coloured than the female especially when the fish are in <a class="link_glossary" href="http://stackoverflow.com/glossary/s/spawning" rel="/glossary/s/spawning?hover=true">spawning</a> condition. Most notably the body colouration is more intense, <a class="link_glossary" href="http://stackoverflow.com/glossary/v/ventral" rel="/glossary/v/ventral?hover=true">ventral</a> fins tipped with white, other finnage redder and prominent tubercules develop around the <a class="link_glossary" href="http://stackoverflow.com/glossary/s/snout" rel="/glossary/s/snout?hover=true">snout</a> and head in sexually <a class="link_glossary" href="http://stackoverflow.com/glossary/m/mature" rel="/glossary/m/mature?hover=true">mature</a> specimens.'),
(104409, 2288, 'reproduction', 'We''re not sure if it has been bred in the hobby although it should certainly be possible. Like most cyprinids this <a class="link_glossary" href="http://stackoverflow.com/glossary/s/species" rel="/glossary/s/species?hover=true">species</a> is an <a class="link_glossary" href="http://stackoverflow.com/glossary/e/egg" rel="/glossary/e/egg?hover=true">egg</a>-scattering, continuous spawner that exhibits no <a class="link_glossary" href="http://stackoverflow.com/glossary/p/parental%20care" rel="/glossary/p/parental%20care?hover=true">parental care</a>. That is to say when the fish are in good condition they will <a class="link_glossary" href="http://stackoverflow.com/glossary/s/spawn" rel="/glossary/s/spawn?hover=true">spawn</a> often and in a well-furnished, <a class="link_glossary" href="http://stackoverflow.com/glossary/m/mature" rel="/glossary/m/mature?hover=true">mature</a> <a class="link_glossary" href="http://stackoverflow.com/glossary/a/aquarium" rel="/glossary/a/aquarium?hover=true">aquarium</a> it is feasible that small numbers of <a class="link_glossary" href="http://stackoverflow.com/glossary/f/fry" rel="/glossary/f/fry?hover=true">fry</a> may start to appear without human intervention.\r\n\r\nHowever if you want to increase the yield of <a class="link_glossary" href="http://stackoverflow.com/glossary/f/fry" rel="/glossary/f/fry?hover=true">fry</a> a slightly more controlled approach is required and we suggest using an approach that has proven successful for similar members of the <a class="link_glossary" href="http://stackoverflow.com/glossary/g/genus" rel="/glossary/g/genus?hover=true">genus</a> as a starting point. The adult group can still be conditioned together but one or more small, say 18" x 10" x 10"/45cm x 25cm x 25cm/29.5 <a class="link_glossary" href="http://stackoverflow.com/glossary/l/litre" rel="/glossary/l/litre?hover=true">litre</a> containers should also be set up and half-filled with water. These should be very dimly lit with the <a class="link_glossary" href="http://stackoverflow.com/glossary/b/base" rel="/glossary/b/base?hover=true">base</a> either left bare or covered with some kind of mesh of a large enough grade so that any eggs that fail to adhere to the plant can pass through but small enough so that the adults cannot reach them. The widely available plastic ''grass''-<a class="link_glossary" href="http://stackoverflow.com/glossary/t/type" rel="/glossary/t/type?hover=true">type</a> matting can also be used and works very well. A decent-sized clump of Java moss or other fine-leaved plant should also be added filling perhaps half the available space. The water should be around <a class="link_glossary" href="http://stackoverflow.com/glossary/n/neutral" rel="/glossary/n/neutral?hover=true">neutral</a> <a class="link_glossary" href="http://stackoverflow.com/glossary/p/pH" rel="/glossary/p/pH?hover=true">pH</a>, <a class="link_glossary" href="http://stackoverflow.com/glossary/G/GH" rel="/glossary/G/GH?hover=true">gH</a> &lt;8, with a slightly raised temperature of 75 - 80°F. A small air-powered <a class="link_glossary" href="http://stackoverflow.com/glossary/s/sponge%20filter" rel="/glossary/s/sponge%20filter?hover=true">sponge filter</a> bubbling away very gently is all that is needed in terms of filtration.\r\n\r\nWhen the adult fish are well-conditioned and the females appear full of eggs a single <a class="link_glossary" href="http://stackoverflow.com/glossary/p/pair" rel="/glossary/p/pair?hover=true">pair</a> should then be introduced to each container. If conditions are to their liking they should <a class="link_glossary" href="http://stackoverflow.com/glossary/s/spawn" rel="/glossary/s/spawn?hover=true">spawn</a> the following morning. Be sure to provide plenty of cover for the female as the male may be quite aggressive in his pursuit of her. In some cases she might even require a period of post-<a class="link_glossary" href="http://stackoverflow.com/glossary/s/spawning" rel="/glossary/s/spawning?hover=true">spawning</a> rehabilitation in a <a class="link_glossary" href="http://stackoverflow.com/glossary/t/tank" rel="/glossary/t/tank?hover=true">tank</a> that does not contain any males.\r\n\r\nThe adults will eat the eggs given the chance and should be removed as soon as any are noticed. Incubation in <em>Puntius</em> eggs is temperature-dependant to an extent but usually takes between 20 and 48 hours with the young free-swimming 24 to 48 hours later. Initial food should be <em>Paramecium</em> or similar introducing <em><a class="link_glossary" href="http://stackoverflow.com/glossary/A/Artemia" rel="/glossary/A/Artemia?hover=true">Artemia</a></em> <a class="link_glossary" href="http://stackoverflow.com/glossary/n/nauplii" rel="/glossary/n/nauplii?hover=true">nauplii</a> and/or <a class="link_glossary" href="http://stackoverflow.com/glossary/m/microworm" rel="/glossary/m/microworm?hover=true">microworm</a> once the <a class="link_glossary" href="http://stackoverflow.com/glossary/f/fry" rel="/glossary/f/fry?hover=true">fry</a> are large enough to accept them.'),
(104410, 2288, 'misc_notes', 'This beautiful <a class="link_glossary" href="http://stackoverflow.com/glossary/s/species" rel="/glossary/s/species?hover=true">species</a> is not often seen in the hobby, presumably as a result of its limited distribution, and tends to command a relatively high price when available. It may be seen on sale under the trade names ''Maharaja'' or ''Khavli'' <a class="link_glossary" href="http://stackoverflow.com/glossary/b/barb" rel="/glossary/b/barb?hover=true">barb</a>.\r\n\r\nThe <a class="link_glossary" href="http://stackoverflow.com/glossary/g/genus" rel="/glossary/g/genus?hover=true">genus</a> <em>Puntius</em> is currently viewed as something of a catch-all for well over 100 <a class="link_glossary" href="http://stackoverflow.com/glossary/s/species" rel="/glossary/s/species?hover=true">species</a> of small cyprinid. Most experts agree that a full <a class="link_glossary" href="http://stackoverflow.com/glossary/r/revision" rel="/glossary/r/revision?hover=true">revision</a> is required, with the likely outcome that many <a class="link_glossary" href="http://stackoverflow.com/glossary/s/species" rel="/glossary/s/species?hover=true">species</a> will be placed into new or different <a class="link_glossary" href="http://stackoverflow.com/glossary/g/genera" rel="/glossary/g/genera?hover=true">genera</a>. When describing the <a class="link_glossary" href="http://stackoverflow.com/glossary/g/genus" rel="/glossary/g/genus?hover=true">genus</a> in 1822, Hamilton identified the defining characteristics as: "absence or presence of <a class="link_glossary" href="http://stackoverflow.com/glossary/m/maxillary" rel="/glossary/m/maxillary?hover=true">maxillary</a> only or <a class="link_glossary" href="http://stackoverflow.com/glossary/r/rostral" rel="/glossary/r/rostral?hover=true">rostral</a> and <a class="link_glossary" href="http://stackoverflow.com/glossary/m/maxillary" rel="/glossary/m/maxillary?hover=true">maxillary</a> barbels; <a class="link_glossary" href="http://stackoverflow.com/glossary/d/dorsal" rel="/glossary/d/dorsal?hover=true">dorsal</a> <a class="link_glossary" href="http://stackoverflow.com/glossary/f/fin" rel="/glossary/f/fin?hover=true">fin</a> with last <a class="link_glossary" href="http://stackoverflow.com/glossary/s/simple%20ray" rel="/glossary/s/simple%20ray?hover=true">simple ray</a> <a class="link_glossary" href="http://stackoverflow.com/glossary/s/serrate" rel="/glossary/s/serrate?hover=true">serrate</a> or entire, <a class="link_glossary" href="http://stackoverflow.com/glossary/b/branched%20rays" rel="/glossary/b/branched%20rays?hover=true">branched rays</a> usually 8; anal <a class="link_glossary" href="http://stackoverflow.com/glossary/f/fin" rel="/glossary/f/fin?hover=true">fin</a> with last <a class="link_glossary" href="http://stackoverflow.com/glossary/s/simple%20ray" rel="/glossary/s/simple%20ray?hover=true">simple ray</a> entire, <a class="link_glossary" href="http://stackoverflow.com/glossary/b/branched%20rays" rel="/glossary/b/branched%20rays?hover=true">branched rays</a> usually 5; <a class="link_glossary" href="http://stackoverflow.com/glossary/l/lateral%20line" rel="/glossary/l/lateral%20line?hover=true">lateral line</a> complete or incomplete, <a class="link_glossary" href="http://stackoverflow.com/glossary/l/lateral" rel="/glossary/l/lateral?hover=true">lateral</a>-line scales 17-36 in row; <a class="link_glossary" href="http://stackoverflow.com/glossary/c/cephalic" rel="/glossary/c/cephalic?hover=true">cephalic</a> <a class="link_glossary" href="http://stackoverflow.com/glossary/c/cutaneous" rel="/glossary/c/cutaneous?hover=true">cutaneous</a> <a class="link_glossary" href="http://stackoverflow.com/glossary/p/papillae" rel="/glossary/p/papillae?hover=true">papillae</a> minute or absent; <a class="link_glossary" href="http://stackoverflow.com/glossary/p/pharyngeal%20teeth" rel="/glossary/p/pharyngeal%20teeth?hover=true">pharyngeal teeth</a> in 3 rows, usually 2,3,5/5,3,2; colour pattern extremely variable." All the <a class="link_glossary" href="http://stackoverflow.com/glossary/s/species" rel="/glossary/s/species?hover=true">species</a> currently in the <a class="link_glossary" href="http://stackoverflow.com/glossary/g/genus" rel="/glossary/g/genus?hover=true">genus</a> are <a class="link_glossary" href="https://stackoverflow.com/glossary/n/native" rel="/glossary/n/native?hover=true">native</a> to Southeast Asia, India and Sri Lanka.\r\n\r\nThe other main source of confusion with <em>Puntius</em> is that some authors do not recognise all the member <a class="link_glossary" href="http://stackoverflow.com/glossary/s/species" rel="/glossary/s/species?hover=true">species</a> as such, rather following Walter Rainboth (1996) and preferring to place some into the alternative <a class="link_glossary" href="http://stackoverflow.com/glossary/g/genus" rel="/glossary/g/genus?hover=true">genus</a> <em>Systomus</em>. Rainboth proposed that <em>Systomus</em> should be reinstated (it was first erected in the 19th century) as a valid <a class="link_glossary" href="http://stackoverflow.com/glossary/g/genus" rel="/glossary/g/genus?hover=true">genus</a> on account of the fact that in its current state <em>Puntius</em> would seem to constitute a <a class="link_glossary" href="http://stackoverflow.com/glossary/p/polyphyletic" rel="/glossary/p/polyphyletic?hover=true">polyphyletic</a> grouping i.e. not all of its members appear to have descended from the same common ancestor. The defining characteristics of a <em>Systomus</em> <a class="link_glossary" href="http://stackoverflow.com/glossary/s/species" rel="/glossary/s/species?hover=true">species</a> are (according to Rainboth) a <a class="link_glossary" href="http://stackoverflow.com/glossary/s/serrated" rel="/glossary/s/serrated?hover=true">serrated</a> (vs. smooth in <em>Puntius</em>) <a class="link_glossary" href="http://stackoverflow.com/glossary/d/dorsal" rel="/glossary/d/dorsal?hover=true">dorsal</a> <a class="link_glossary" href="http://stackoverflow.com/glossary/s/spine" rel="/glossary/s/spine?hover=true">spine</a>, the presence of 2 or 4 barbels (vs. always 2) and less than 12 <a class="link_glossary" href="http://stackoverflow.com/glossary/g/gill" rel="/glossary/g/gill?hover=true">gill</a> rakers (vs. 12-20). SF tentatively lists all <a class="link_glossary" href="http://stackoverflow.com/glossary/s/species" rel="/glossary/s/species?hover=true">species</a> as <em>Puntius</em> at present.'),
(167927, 2288, 'etymology', ''),
(104421, 2288, 'attached_media', 'a:4:{i:0;s:5:"22881";i:1;s:5:"22882";i:2;s:5:"22883";i:3;s:5:"22884";}'),
(150578, 2288, 'references', 'a:2:{i:0;a:5:{s:13:"ref_doc_title";s:67:"Assemblage structure of stream fishes in the Western Ghats (India).";s:12:"ref_pub_year";s:0:"";s:13:"ref_page_nums";s:5:"1-31.";s:14:"ref_publishers";s:17:"Hydrobiologia 430";s:11:"ref_authors";s:19:"Arunachalam M. 2000";}i:1;a:5:{s:13:"ref_doc_title";s:0:"";s:12:"ref_pub_year";s:0:"";s:13:"ref_page_nums";s:0:"";s:14:"ref_publishers";s:16:"www.fishbase.org";s:11:"ref_authors";s:0:"";}}'),
(167925, 2288, '_edit_lock', '1341244017:4'),
(167928, 2288, 'species_author', 'Silas'),
(167929, 2288, 'year_described', '1953'),
(167930, 2288, 'beginner_suitability', '3'),
(167931, 2288, 'type_of_fish', '2')

wp_posts

INSERT INTO `wp_posts` (`ID`, `post_author`, `post_date`, `post_date_gmt`, `post_content`, `post_title`, `post_excerpt`, `post_status`, `comment_status`, `ping_status`, `post_password`, `post_name`, `to_ping`, `pinged`, `post_modified`, `post_modified_gmt`, `post_content_filtered`, `post_parent`, `guid`, `menu_order`, `post_type`, `post_mime_type`, `comment_count`) VALUES
(2288, 4, '2012-03-13 13:24:32', '2012-03-13 13:24:32', '', 'Puntius sahyadriensis', 'This beautiful species is not often seen in the hobby, presumably as a result of its limited distribution, and tends to command a relatively high price when available. It may be seen on sale under the trade names &#039;Maharaja&#039; or &#039;Khavli&#039; barb.\r\n\r\nThe genus Puntius is currently viewed as something of a catch-all for well over 100 species of small cyprinid. Most experts agree that a full revision is required, with the likely outcome that many species will be placed into new or di...', 'publish', 'open', 'open', '', 'puntius-sahyadriensis', '', '', '2012-07-02 16:48:43', '2012-07-02 15:48:43', '', 0, 'http://www.seriouslyfish.com/?post_type=species&#038;p=2288', 0, 'species', '', 0);

Como puede ver, el contenido de los perfiles de especies es increíblemente extenso. Con suerte, esto también le mostrará el tipo de enlace que me gustaría usar para obtener mis ventanas emergentes:

<a class="link_glossary" href="https://stackoverflow.com/glossary/n/native" rel="/glossary/n/native?hover=true">native</a>

Mi problema es este: el filtro que estoy ejecutando en este momentocuyos detalles se pueden encontrar en la primera publicación vinculada, o directamente aquí para mi filter.php expediente, simplemente no es lo suficientemente eficiente para trabajar con tantos campos de base de datos y tanta información.

El filtro funcionó cuando comencé a usar nuestro nuevo CMS impulsado por WordPress, ya que lo ejecuté en una instalación local de WAMP cuando importé los datos. Tomó casi 40 minutos procesar todos los datos.

Mi tl; dr pregunta es esta: ¿Cómo puedo hacer para crear un filtro de contenido eficiente?teniendo en cuenta la cantidad de datos y el número de campos de la base de datos?

Question 2

Estructura de la base de datos

wp_postmeta (meta_id, post_id, meta_key, meta_value)
wp_glossary (glossary_id, glossary_key, glossary_value)
wp_relation (meta_id, glossary_id)

wp_relation almacena la relación entre wp_glossary y wp_postmeta. El siguiente es un ejemplo de una entrada de wp_postmeta:

Mantenimiento
Tanque debe estar bien plantado con plantas flotantes también se utilizan. Se debe proporcionar una gran cantidad de escondites como este especies le gusta esconderse durante el día. Bogwoodlas cuevas de roca y las tuberías de PVC son adecuadas para este propósito. Arena debe usarse como sustrato ya que a las anguilas espinosas a menudo les gusta enterrarse. Una iluminación más tenue animará a los peces a salir de sus escondites con más frecuencia. Se requiere una capucha ajustada ya que la anguila puede encontrar su camino a través de los espacios más pequeños. El flujo de agua debe ser bastante suave ya que el pez habita principalmente en áreas de aguas tranquilas en la naturaleza.

El siguiente es un ejemplo de las entradas de wp_glossary

glossary_id | glossary_key | glossary_value
         101   tank           ......
         102   species        ......
         103   bogwood        ......
         104   sand           ......
         105   substrate      ......

El siguiente es un ejemplo de cómo wp_relation mantiene la relación de glosario y postmeta. Suponiendo que la identificación de postmeta anterior es 405

glossary_id | meta_id 
         101   405       
         102   405  
         103   405    
         104   405       
         105   405

Perfil de salida

Una vez que pueda mantener la estructura de la base de datos anterior, puede construir fácilmente un enlace de glosario para cada wp_postmeta buscando la identificación de la entrada de wp_postmeta en wp_relation. Entonces, usando str_replace para llenar el enlace a su contenido.

Para aumentar el rendimiento, puede almacenar en caché el contenido de los resultados en HTML. Establezca la caducidad de la memoria caché en 1 o 2 días (depende de la frecuencia con la que actualice su glosario y el tráfico de su sitio). Puedes usar memcached, file o database para almacenar su caché.

Mantener la estructura de la base de datos

La tarea más difícil es mantener nuestra relación con la base de datos. Hay dos acciones principales que alteran la relación de la base de datos.

Alterar glosario

Agregar nuevo glosario: Busque a través de wp_postmeta y construya la relación.
Eliminar glosario: Eliminar toda relación entre este glosario y otros wp_postmeta
Editar glosario: Es una combinación de eliminar glosario y agregar nuevo glosario.

Alterar Postmeta

Añadir/Editar Postmeta: Reconstruye la relación entre el glosario y su contenido. A continuación se muestra el algoritmo que se puede utilizar para construir la relación. Creo que hay otras formas mejores de hacerlo. El código no está completo (aún no lo he probado), pero es suficiente para que entiendas el algoritmo.

// Initialize text to add glossary link
$desc = "Tank should be well planted with floating plants also used. 
        An abundance of hiding places should be provided as this species 
        likes to hide away during the day. Bogwood, rock caves and PVC 
        piping are all suitable for this purpose. Sand should be used as 
        substrate as the spiny eels often like to bury themselves. 
        Dimmer lighting will encourage the fish to venture from 
        its hiding places more often. A close-fitting hood is required 
        as the eel can find its way through the smallest of gaps. Water 
        flow should be fairly gentle as the fish mainly inhabits 
        areas of still water in the wild.";

// Split text into list of words
$split = preg_split("/[.,\s]+/", $desc);

// Frequency English words
$freq['will'] = true;
$freq['also'] = true;
$freq['with'] = true;
$freq['about'] = true;
$freq['back'] = true;
$freq['been'] = true;
$freq['were'] = true;
$freq['want'] = true;

// Get list of unqiue word and elimate unnessary words
foreach($split as $value) {
    $value = strtolower($value);

    if (strlen($value) < 4) continue;
    if (is_numeric($value)) continue;
    if (isset($freq[$value])) continue;

    if (!isset($hash[$value])) $hash[$value] = true;
}

// Join the list for search
$keys = "";
foreach ($hash as $key => $value)
    $keys .= "^{$key}|";
$keys = rtrim($keys, '|');

// Search for list of glossary
$glossary;
$result = mysql_query("SELECT glossary_id,glossary_key FROM wp_glossary  WHERE gossary_key REGEXP '{$keys}'")
if ($result) {
    while($row = mysql_fetch_row($result)) {
        if (strpos($desc, $row[1]) !== false)
            $glossary[$row[0]] = $row[1];
    }
}

// You can start to construct the relation from this $glossary
// by loop throught it one by one and insert it into wp_relation
print_r($glossary);

Quitar Postmeta : Eliminar la relación entre este postmeta y el glosario.

Beneficios adicionales

Digamos que debe encontrar el perfil de especie que usa el glosario “bogwood”. Puede rastrearlo fácilmente a través de wp_relation.

Preguntas

1. En primer lugar, ¿cómo afectará un str_replace a mis perfiles de especies que tienen un código existente en ellos, es decir, un código href o img?

No afecta sus perfiles de especies si no hay a link al glosario y nombre de la especie.

2. En segundo lugar, ha mencionado las entradas del glosario, pero no los nombres de especies/género de especies un poco más complejos. ¿Usaría una tabla separada para esos, o lo incorporaría todo en uno?

Puede tener una tabla separada para almacenar la relación entre el nombre de la especie y el postmeta o simplemente puede tratar los nombres de las especies como un término del glosario con una bandera especial (que necesita cambiar la forma en que está estructurado el glosario)

3. Entonces, ¿cómo especificaría la diferencia (clase diferente en la etiqueta a) entre las entradas del glosario y los perfiles de especies?

La respuesta depende del método que utilice para sus segundas preguntas.

Question 3

Aquí hay otro pensamiento, haga el marcado en el cliente.

Extraiga las taxonomías como archivos de texto y almacene en caché los “términos de transformación”.

guppie
salmon

Haga que JS los revise mientras el lector lee la página y reescriba las URL;

<a href="https://stackoverflow.com/section/guppie">guppie</a> and a <a href="http://stackoverflow.com/section/salmon">salmon</a>

Debe haber más en la red sobre este método desde una vieja discusión que tuve una vez aquí.

También me topé con este tut en Taxonomías personalizadas de WP que podría ser relevante para su caso.

Question 4

¿Has considerado un Árbol de búsqueda ternario?

Esta es la estructura de datos que prefiero cuando me enfrento al problema de “encontrar x número de palabras en la masa de texto y”.

Ejemplo de árbol construido (aleta, filtro, caudal, galón, llamativo):

        f
       /|\
caudal* i ga
        |  | 
        n* l*
       /    \
    lter*    udy*

* = end node

Y el algoritmo será a lo largo de las líneas de:

Comience la palabra para que coincida con el nodo raíz.
Siempre y cuando las coincidencias de la posición actual se muevan hacia abajo.
Si la posición actual
Si la posición actual > nodo. Mover a la derecha.
Si la palabra está terminada y el nodo es el nodo final. Palabra encontrada.
Si no es posible mover hacia la izquierda/derecha o la palabra termina en un nodo sin fin. Palabra no encontrada.

Aquí está un buen ejemplo.

La ventaja es que el árbol se puede precalcular y almacenar en alguna forma serializada. A menudo, incluso es posible crear o transferir un árbol prediseñado al cliente y hacer la coincidencia en el navegador.