Tag: Query

WordPress primitive query returns = array

i am trying to primitive query form this wp_query:

$  query_v = array(     'post_type' => array('pro'),     'posts_per_page'    => -1,     'orderby' => $  count_visit,     'order' => 'ASC',     'tax_query' => array(         array(             'taxonomy' => 'servizi_pro',             'field' => 'slug',             'terms' => $  art_p,             'parent' => 0         )     ) );

the problem with this wp_query is that it does not sort me by the number of post views counter that I downloaded as an additional plugin, and I am trying to implement in a primita wordpress query, the wp_query with the addition of the instructions to display the type of custom posts based on the number of views:

<?php global $  wpdb; $  cpt = "pro";  $  q = $  wpdb->get_results(     $  wpdb->prepare(" SELECT * FROM {$  wpdb->prefix}term_relationships  LEFT JOIN {$  wpdb->prefix}terms ON {$  wpdb->prefix}term_relationships.term_taxonomy_id = {$  wpdb->prefix}terms.term_id  LEFT JOIN term_taxonomy ON {$  wpdb->prefix}term_relationships.term_taxonomy_id = {$  wpdb->prefix}term_taxonomy.term_id  LEFT JOIN {$  wpdb->prefix}posts  ON {$  wpdb->prefix}term_relationships.object_id = {$  wpdb->prefix}posts.ID  LEFT JOIN post_views ON {$  wpdb->prefix}term_relationships.object_id = {$  wpdb->prefix}post_views.id  WHERE  {$  wpdb->prefix}post_views.type = '4'    AND {$  wpdb->prefix}posts.post_type = '$  cpt'  AND  {$  wpdb->prefix}terms.name = '$  art_p'         ") ); ?>  <?php print_r($  q);?> <?php foreach ($  q as $  r){ ?>     <?php echo "{$  r->post_title}"; ?> <?php } ?>

but it always returns to me array (), where am I wrong?

Cluster is causing my query to run SLOWER then before

So I have been stuck on this for 6 hours now and I have no clue what to do. I am doing university homework that requires us to create a unoptomized sql query (does not have to make sense) and then apply index’s and see if it makes it faster (which it did for me, from 0.70 elapsed time to 0.66) and then we had to apply clusters.

I applied clusters and it has now almost doubled the amount taken to finish the query. From 0.70 to 1.15. Below is how I specified my cluster:

CREATE CLUSTER customer2_custid25 (custid NUMBER(8))    SIZE 270  TABLESPACE student_ts;

I tried all my previous times with INITIAL and NEXT but that seemed not to make a difference. Below are the tables:

CREATE TABLE CUSTOMER18 (       CustID         NUMBER(8) NOT NULL,     FIRST_NAME     VARCHAR2(15),     SURNAME     VARCHAR2(15),     ADDRESS     VARCHAR2(20),     PHONE_NUMBER NUMBER(12))       CLUSTER customer2_custid25(CustID);   CREATE TABLE product18(       ProdID     NUMBER(10) NOT NULL,     PName    Varchar2(6),     PDesc    Varchar2(15),     Price    Number(8),     QOH        Number(5));   CREATE TABLE sales18(       SaleID     NUMBER(10) NOT NULL,     SaleDate    DATE,     Qty            Number(5),     SellPrice    Number(10),     CustID        NUMBER(8),     ProdID        NUMBER(10))       CLUSTER customer2_custid25(CustID);     CREATE INDEX customer2_custid_clusterindxqg ON CLUSTER customer2_custid25 TABLESPACE student_ts ;

I also tried taking the tablespace section in the cluster index away.

I followed this formula to help calculate cluster sizes:

"Size of a cluster is the size of a parent row + (size of Child row * average number of children). "

This brought me to the size of 270. However, after testing sizes (going up 20) from 250 to 350 I found 320 to be the fastest at 1.15.

No matter what I try, I can not for the love of me get it lower then my base query times.

Other students have done the same and halved their query time.

All help is really appreciated.

WP Query | search by sub-categories

I have a small problem with the functioning of a search bar that gives the user the possibility to find professionals based on their job categories.

I explain: I am talking about a portal that allows the user to be able to carry out a search as written above.

How I managed the programming: if it is a portal where there are professionals, I created the custom post type: Pro

To specify the services that these professionals are able to perform, I created a plugin that allows me by specifying in the array which custom post types to grant the possibility to implement taxonomies:

register_taxonomy( 'categoria', array( 'pro' ), $  args );

now very simply I have implemented a form on a page:

<div id="search_video"  class="container-fluid">     <div style="text-align: center;background-color: #fff;" class="col-md-12">         <form name="NOME_FORM" method="get" id="advanced-searchform" role="search" action="<?php echo esc_url( home_url( '/results' ) ); ?>">             <div class="input-group mb-3">                 <div class="input-group-prepend">                     <span class="input-group-text"><i class="fa fa-search"></i></span>                 </div>                 <input type="text" id="art" name="art" class="form-control">             </div>         </form>     </div>     </div> </div>

pointing to the results page.

to display the results I used this query:

<?php $  art_p = $  _GET['art'];         $  query_v = array(             'post_type' => array('pro'),             'posts_per_page'    => -1,             'orderby' => 'meta_value',             'order' => 'ASC',             'tax_query' => array(                 array(                     'taxonomy' => 'categoria',                     'terms' => $  art_p,                 )             )         );         $  res_v = new WP_Query( $  query_v );         ?>

now the problem:

I have implemented two test professional profiles, in the services category, I have specified as a sub-category of services for professional A:

  • creation of websites

and for professional B:

  • logo creation

but if I look in the website creation bar: it gives me both professionals as a result and this is not good.

So my question is how can I filter the results so that the user search is based on the sub-category of services and only brings out the professionals who own that sub-category, what did I do wrong with my query?

Same query has different execution plans in Prod vs Test

I have a query that runs in Prod every 30 minutes. Up until yesterday it runs in seconds. Suddenly it’s taking 7 minutes.

I copied the table to Test, created the indexes & gathered statistics. It runs in seconds.

In Prod, even after rebuilding indexes and updating statistics on the table with fullscan, it’s still not performing any better. The actual execution plan in prod looks very different to test and is showing actual reads on one part of the query to be 400 million rows (there are only 1.5 million in the table).

I ran it in test with no indexes (~5 seconds) and then with indexes and all run sub-second.

In Prod, I’ve dropped the Primary Key / Clustered index, updated the statistics (update statistics interface.statsload) and rebuilt it and still it takes 8-10 minutes to run.

Also tried dropping the PK and running it again. Actual Plan shows a very thick pipe in one step with about 9 million actual rows on a full scan. When I do a select Count(*) on the table, it only shows 1.5M rows. Why is that ? I’m sure that’s feeding into it somehow.

I’m baffled. Any pointers on where I could start to look for a root cause here maybe ? I can post more info (plans, table columns, indexes) if needed.

How to Override Page Template if URL matches query?

I have 4 Page Templates that my theme uses for all the pages – Default Template, Template One, Template Two and Template Three.

When the relevant Page Template is selected, it loads an Advanced Custom Fields Group which enables the user to build/design a specific page – i.e. Template Two is for an F.A.Q page, Template Three is for a photo gallery.

I have added 4 new links in the Admin Sidebar which directs the user to the "Add New Page" URL – "post-new.php?post_type=page". For each link, it appends the name of the template at the end of the URL i.e. "post-new.php?post_type=page&template=three".

I would like to have the Edit Page load the correct Page Template depending on the template dictated in the URL – "template=three" would load "Template Three".

I am able to do a check with the URL and form an if statement with the below code —

list($  uri, $  templateURL) = explode('&', $  _SERVER['REQUEST_URI']);     if ( $  templateURL == "template=services" ) {    ...

I am struggling with how to hook this into functions.php. I have tried editing the below ( https://wordpress.stackexchange.com/a/231503/182411 ) but it isn’t working:

function wpse196289_default_page_template() {   global $  post;   list($  uri, $  templateURL) = explode('&', $  _SERVER['REQUEST_URI']);     if ( 'page' == $  post->post_type )     && ( $  templateURL == "template=services" )     {       $  post->page_template = "page-mytemplate.php";     } } add_action('add_meta_boxes', 'wpse196289_default_page_template', 1);

Query in do loop

Why is the following code not working? :

Do[gm = 200*N[Pi];  s1 = k^2 + (d - ((g1^2)*points[i, 2, 1]/wm))^2 + 2*k*gm + wm^2;  s2 = (k^2 + (d - ((g1^2)*points[i, 2, 1]/wm))^2)*gm + 2*k*wm^2;  s3 = (k^2 + (d - ((g1^2)*points[i, 2, 1]/wm))^2)*(wm^2) - (d - (g1^2*         points[i, 2, 1]/wm))*wm*(g1^2)*(2 points[i, 2, 1]);  If[(2 k + gm) s1 > s2, Print[True], Print[False]];  If[s1*s2 (2 k + gm) > s2^2 + (2 k + gm)^2*(s3), Print[True],    Print[False]];  If[s1 > 0, Print[True], Print[False]];  If[s2 > 0, Print[True], Print[False]];  If[s3 > 0, Print[True], Print[False]], {i, 11}]

Please suggest ways to tweak this or an alternative method.

Hint: There is a column named “pma_timestamp” in table “table_a”, but it cannot be referenced from this part of the query

hi i want to use a user defined function so that while inserting values in column i want to insert last_day of the month in one column by referencing other column in same table.

my code is-

insert into table_A (id,pma_timestamp,monthend) values (103,’2017-08-15′,last_day(pma_timestamp));

but getting error. how to resolve this

PostgreSQL: query slow, planner estimates 0.01-0.1 of actual results

I will preface this by saying I’m not well-versed in SQL at all. I mainly work with ORMs, and this recent headache has brought me to dive into the world of queries, planners, etc..

A very common query is behaving weirdly on my website. I have tried various techniques to solve it but nothing is really helping, except narrowing down the released_date field from 30 days to 7 days. However, from my understanding the tables we’re talking about aren’t very big and PostgreSQL should satisfy my query in acceptable times.

Some statistics:

core_releasegroup row count: 3,240,568

core_artist row count: 287,699

core_subscription row count: 1,803,960

Relationships:

Each ReleaseGroup has M2M with Artist, each Artist has M2M with UserProfile through Subscription. I’m using Django which auto-creates indices on foreign-keys, etc..

Here’s the query:

SELECT "core_releasegroup"."id", "core_releasegroup"."title", "core_releasegroup"."type", "core_releasegroup"."release_date", "core_releasegroup"."applemusic_id", "core_releasegroup"."applemusic_image", "core_releasegroup"."geo_apple_music_link", "core_releasegroup"."amazon_aff_link", "core_releasegroup"."is_explicit", "core_releasegroup"."spotify_id", "core_releasegroup"."spotify_link"  FROM "core_releasegroup"  INNER JOIN "core_artist_release_groups"  ON ("core_releasegroup"."id" = "core_artist_release_groups"."releasegroup_id")  WHERE ("core_artist_release_groups"."artist_id"  IN  (SELECT U0."artist_id" FROM "core_subscription" U0 WHERE U0."profile_id" = 1)  AND "core_releasegroup"."type" IN ('Album', 'Single', 'EP', 'Live', 'Compilation', 'Remix', 'Other')  AND "core_releasegroup"."release_date" BETWEEN '2020-08-20'::date AND '2020-10-20'::date);

Here’s the table schema:

CREATE TABLE public.core_releasegroup (     id integer NOT NULL,     created_date timestamp with time zone NOT NULL,     modified_date timestamp with time zone NOT NULL,     title character varying(560) NOT NULL,     type character varying(30) NOT NULL,     release_date date,     applemusic_id character varying(512),     applemusic_image character varying(512),     applemusic_link character varying(512),     spotify_id character varying(512),     spotify_image character varying(512),     spotify_link character varying(512),     is_explicit boolean NOT NULL,     spotify_last_refresh timestamp with time zone,     spotify_next_refresh timestamp with time zone,     geo_apple_music_link character varying(512),     amazon_aff_link character varying(620) );

I have an index both on type, release_date, and applemusic_id.

Here’s the PostgreSQL execution plan: (notice the estimates)

 Nested Loop  (cost=2437.52..10850.51 rows=4 width=495) (actual time=411.911..8619.311 rows=362 loops=1)    Buffers: shared hit=252537 read=29104    ->  Nested Loop  (cost=2437.09..10578.84 rows=569 width=499) (actual time=372.265..8446.324 rows=36314 loops=1)          Buffers: shared hit=143252 read=29085          ->  Bitmap Heap Scan on core_releasegroup  (cost=2436.66..4636.70 rows=567 width=495) (actual time=372.241..7707.466 rows=32679 loops=1)                Recheck Cond: ((release_date >= '2020-08-20'::date) AND (release_date <= '2020-10-20'::date) AND ((type)::text = ANY ('{Album,Single,EP,Live,Compilation,Remix,Other}'::text[])))                Heap Blocks: exact=29127                Buffers: shared hit=10222 read=27872                ->  BitmapAnd  (cost=2436.66..2436.66 rows=567 width=0) (actual time=366.750..366.750 rows=0 loops=1)                      Buffers: shared hit=15 read=8952                      ->  Bitmap Index Scan on core_releasegroup_release_date_03a267f7  (cost=0.00..342.46 rows=16203 width=0) (actual time=8.834..8.834 rows=32679 loops=1)                            Index Cond: ((release_date >= '2020-08-20'::date) AND (release_date <= '2020-10-20'::date))                            Buffers: shared read=92                      ->  Bitmap Index Scan on core_releasegroup_type_58b6243d_like  (cost=0.00..2093.67 rows=113420 width=0) (actual time=355.071..355.071 rows=3240568 loops=1)                            Index Cond: ((type)::text = ANY ('{Album,Single,EP,Live,Compilation,Remix,Other}'::text[]))                            Buffers: shared hit=15 read=8860          ->  Index Scan using core_artist_release_groups_releasegroup_id_cea5da71 on core_artist_release_groups  (cost=0.43..10.46 rows=2 width=8) (actual time=0.018..0.020 rows=1 loops=32679)                Index Cond: (releasegroup_id = core_releasegroup.id)                Buffers: shared hit=133030 read=1213    ->  Index Only Scan using core_subscription_profile_id_artist_id_a4d3d29b_uniq on core_subscription u0  (cost=0.43..0.48 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=36314)          Index Cond: ((profile_id = 1) AND (artist_id = core_artist_release_groups.artist_id))          Heap Fetches: 362          Buffers: shared hit=109285 read=19  Planning Time: 10.951 ms  Execution Time: 8619.564 ms

Please note that the above is a stripped down version of the actual query I need. Because of its unbearable slowness, I’ve stripped down this query to a bare-minimum and reverted to doing some filtering and ordering of the returned objects in Python (which I know is usually slower). As you can see, it’s still very slow.

After a while, probably because the memory/cache are populated, this query becomes much faster:

 Nested Loop  (cost=2437.52..10850.51 rows=4 width=495) (actual time=306.337..612.232 rows=362 loops=1)    Buffers: shared hit=241776 read=39865 written=4    ->  Nested Loop  (cost=2437.09..10578.84 rows=569 width=499) (actual time=305.216..546.749 rows=36314 loops=1)          Buffers: shared hit=132503 read=39834 written=4          ->  Bitmap Heap Scan on core_releasegroup  (cost=2436.66..4636.70 rows=567 width=495) (actual time=305.195..437.375 rows=32679 loops=1)                Recheck Cond: ((release_date >= '2020-08-20'::date) AND (release_date <= '2020-10-20'::date) AND ((type)::text = ANY ('{Album,Single,EP,Live,Compilation,Remix,Other}'::text[])))                Heap Blocks: exact=29127                Buffers: shared hit=16 read=38078 written=4                ->  BitmapAnd  (cost=2436.66..2436.66 rows=567 width=0) (actual time=298.382..298.382 rows=0 loops=1)                      Buffers: shared hit=16 read=8951                      ->  Bitmap Index Scan on core_releasegroup_release_date_03a267f7  (cost=0.00..342.46 rows=16203 width=0) (actual time=5.619..5.619 rows=32679 loops=1)                            Index Cond: ((release_date >= '2020-08-20'::date) AND (release_date <= '2020-10-20'::date))                            Buffers: shared read=92                      ->  Bitmap Index Scan on core_releasegroup_type_58b6243d_like  (cost=0.00..2093.67 rows=113420 width=0) (actual time=289.917..289.917 rows=3240568 loops=1)                            Index Cond: ((type)::text = ANY ('{Album,Single,EP,Live,Compilation,Remix,Other}'::text[]))                            Buffers: shared hit=16 read=8859          ->  Index Scan using core_artist_release_groups_releasegroup_id_cea5da71 on core_artist_release_groups  (cost=0.43..10.46 rows=2 width=8) (actual time=0.003..0.003 rows=1 loops=32679)                Index Cond: (releasegroup_id = core_releasegroup.id)                Buffers: shared hit=132487 read=1756    ->  Index Only Scan using core_subscription_profile_id_artist_id_a4d3d29b_uniq on core_subscription u0  (cost=0.43..0.48 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=36314)          Index Cond: ((profile_id = 1) AND (artist_id = core_artist_release_groups.artist_id))          Heap Fetches: 362          Buffers: shared hit=109273 read=31  Planning Time: 1.088 ms  Execution Time: 612.360 ms

This is still slow in SQL terms (I guess?), but much more acceptable. The problem is, even though this is a very common view in my web-app (an often executed query), it is still not kept around in RAM/cache, and so I see these huge response-time spikes too often.

I’ve tried every combination of constructing those queries. My last attempt is to see whether the planner estimations are to blame here, and if they’re fixable. If not, I’ll start considering de-normalization.

Or is there something else I’m missing?

WordPress + Vue — Single page app giving me 404s when I use query params

I’ve got my rest api powered site working really well with Vue. Except for the issue of WordPress and urls.

Site: http://mytely.ca/

Search for a name or click on a name and the url updates. Now refresh the page. Everything still works, in that Vue will take over and do all the requests and set things up. But I’m getting a 404 on every page like that.

I don’t really know anything about this whole thing. I tried setting up a redirect plugin but that does what it says — it redirects from http://mytely.ca/&page=1&search=John%20Doe back to http://mytely.ca, which isn’t what I want.

I basically want WordPress to ignore the urls completely — just have index.php (and hence Vue) takeover everything. Basically, not 404 and just pass on through with the url.

I’m not currently using Vue Router at all in my app — just appending query strings and using popstate. I’m wondering if I actually need to be using Vue Router to handle this.