I need help with the following output. Tried case, Distinct, Group By, failed. Join might help, but still couldn’t write a query with join.
Tag: Query
How can I track down an issue with a locked table, endless query?
I have a table that is periodically getting locked in a manner that I don’t quite understand. I cannot do the following:
select * from thetable select count(*) from thetable There are roughly 2,000 records.
I can do the following:
select top 2000 * from thetable select * from thetable where ID = etc. Going backwards to find a new record that perhaps was problematic, incrementing the count in chunks until I can finally reproduce it again:
select top 1500 * from thetable order by ID desc select top 1550.... etc. and eventually it gets locked and never finishes the query. Query never finishes… have waited 10 minutes. Only resolution is to restart the service.
The related stored procedure that I thought caused the problem I ran manually (it interacts with this table) and the longest time it took was roughly 45 seconds. This particular procedure goes through many phases and is wrapped in a transaction with a try/catch/rollback/commit. There is no explicit locking set on the procedure.
Any direction or guidance to track down the root issue is greatly appreciated.
Query to check if column value appears more than once then apply condition
I have a PostgreSQL query:
SELECT DISTINCT ON ("contract"."contract_id") "contract"."id" FROM "contract_versions" "contract" WHERE "contract"."client_id" = 1 GROUP BY "contract"."contract_id", "contract"."id" ORDER BY "contract"."contract_id", "contract"."change_effective_date" DESC I want to add something like if contract_id occurs more than once then change_effective_date >= now()
I tried using:
SELECT DISTINCT ON ("contract"."contract_id") "contract"."id", COUNT("contract"."contract_id") AS cnt FROM "contract_versions" "contract" WHERE "contract"."client_id" = 1 AND CASE WHEN "cnt" > 1 THEN "contract"."change_effective_date" <= now() END GROUP BY "contract"."contract_id", "contract"."id" ORDER BY "contract"."contract_id", "contract"."change_effective_date" DESC but its throwing an error column “cnt” does not exist
Thanks
A SQL query optimzation issue with ORDER BY
An SQL query optimzation issue with ORDER BY
Current status I have a database of Chess games and their corresponding moves (stored as strings called fens). I have two main tables ‘Game’ and ‘GamePosition’. GamePosition has an index on the fen column and Game has an index on white_elo. I currently have 170471 games and 14813401 positions. I’m running mysql 5.7.28.
Object I’m trying to fetch top rated games based on elo rating of the players. I’ve simplified my query a bit here, but the point and performance problem is the same.
SELECT Game.id FROM Game JOIN GamePosition ON Game.id = game_id WHERE fen = 'rnbqkbnr/pppppppp/8/8/3P4/8/PPP1PPPP/RNBQKBNR' ORDER BY white_elo DESC LIMIT 10 This query tends to be a bit slow (1.2 s) if I get a lot of results (typically the first move gives 67k+ results). Since I plan to exand the database 10x at least, I want to optimize my query. I’ve run a EXPLAIN which shows that it needs to do a filesort before finding the top rated games on all the results. This seems to be the issue. Removing the ORDER BY from the query makes it superfast (0.0008 s). 
Any ideas if it’s possible to optimize the query, or if I could store the data differently?
Kind Regards, Bjorn
Create Post Query to order by a Taxonomy custom meta field
I have a standard WP_Query to search for Posts of a certain Custom Post Type. I am trying to extend this by filtering by a specific custom meta field that was set for a Taxonomy that is attached to the Post.
What I’ve done is created a Taxonomy, and in that Tax I’ve added a custom meta field called “sorting_weight” that is a numerical field. So when admin assign a CPT to this Tax, I would be able to query all CPT entries and then orderly that numerical field.
All the tutorials online are getting confused and thinking the user wants to sort by normal post custom meta. This is not what I want – I want to sort by the CPT’s Taxonomy’s custom meta.
From what I can gather, I can’t even pipe this logic into a normal WP_Query argument? If so, how would I refactor my code to order by this Taxonomy custom meta field “sorting_weight”?
The below shows the general query I am using:
/** * @return array */ protected function returnQuery(): array { $ query = [ 'post_type' => $ this->getPostTypeName(), 'show_posts' => -1, 'hide_empty' => true, 'meta_key' => 'year', 'orderby' => 'meta_value', 'order' => 'DESC' ]; /** * CPT Meta Queries */ if ($ this->getSearchQuery()['appGallerySearch_Category']) { $ query['meta_query'][] = [ 'key' => 'topic', 'value' => $ this->getSearchQuery()['appGallerySearch_Category'], 'compare' => 'LIKE' ]; } if ($ this->getSearchQuery()['appGallerySearch_Program']) { $ query['meta_query'][] = [ 'key' => 'program', 'value' => $ this->getSearchQuery()['appGallerySearch_Program'], 'compare' => 'LIKE' ]; } if ($ this->getSearchQuery()['appGallerySearch_Division']) { $ query['meta_query'][] = [ 'key' => 'divisions', 'value' => $ this->getSearchQuery()['appGallerySearch_Division'], 'compare' => 'LIKE' ]; } if ($ this->getSearchQuery()['appGallerySearch_Year']) { $ query['meta_query'][] = [ 'key' => 'year', 'value' => $ this->getSearchQuery()['appGallerySearch_Year'], 'compare' => 'LIKE' ]; } if ($ this->getSearchQuery()['appGallerySearch_Location']) { $ query['meta_query'][] = [ 'key' => 'region', 'value' => $ this->getSearchQuery()['appGallerySearch_Location'], 'compare' => 'LIKE' ]; } if ($ this->getSearchQuery()['appGallerySearch_Keyword']) { $ query['meta_query'][] = [ 'key' => 'description', 'value' => $ this->getSearchQuery()['appGallerySearch_Keyword'], 'compare' => 'LIKE' ]; } return $ query; } curl query to regular url: basic auth
I’m currently experimenting with the Toggl API https://github.com/toggl/toggl_api_docs/blob/master/chapters/workspaces.md#get-workspace-users
For example, the page states
curl -v -u 1971800d4d82861d8f2c1651fea4d212:api_token -X GET https://www.toggl.com/api/v8/time_entries/current If i enter this in my console (using my api token, not the example token of course) it works and I’m getting back the requested JSON.
If I try to get the json however directly in a browser by restatting the query as
https://1971800d4d82861d8f2c1651fea4d212:api_token@www.toggl.com/api/v8/time_entries/current I’m getting
Access to www.toggl.com was denied You don't have authorization to view this page. HTTP ERROR 403 what am I doing wrong? I thought that user:pw@domain would be the same as curl -u user:pw -X GET domain
Optimization of a query to retrieve records randomly with multiple joins and filters
I have the following schema:
This question was posted also in StackOverflow, but I want to consult also to specialists more focused on DB administration because the nature of my project. Sorry if this is a mistake
Right now, the table Property hold more than 70K records. I’m developing an update to support more than 500 concurrent sessions. The application will support a map a to make the searches, that’s why GeoLocation declares Coordinate as geography data type. Now we have a big problem, because the response time for some queries (the most important ones) is very slow. I mean, the application has to return around 1000 records at once if there are that quantity of results for the specified parameters.
The parameters are distributed on all the tables of the schema (actually, it’s a portion of the schema). Being Features a table which holds all the principal “characteristics” of the properties (# of bedrooms, # of garages, etc).
With that on mind, the query that is taking so much time right now is the following:
DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX); DECLARE @properties TABLE( [ID] INT ) INSERT INTO @properties SELECT p.[Id] FROM[Property] p INNER JOIN[GeoLocation] AS[g] ON[p].[Id] = [g].[PropertyId] INNER JOIN[PropertyFeature] AS[pf] ON[pf].[PropertyId] = [p].[Id] INNER JOIN[Feature] AS[f] ON[pf].[FeatureId] = [f].[Id] WHERE[g].[Address] IS NOT NULL AND(([g].[Address] <> N'') OR[g].[Address] IS NULL) AND[pf].[FeatureId] IN( Select ID from feature where featuretype = 1) GROUP BY p.Id, p.ModificationDate ORDER BY [p].ModificationDate DESC, newid() OFFSET 0 ROWS FETCH NEXT 1000 ROWS ONLY DECLARE @features TABLE( [Name] NVARCHAR(80) ) INSERT INTO @features select Name from feature where FeatureType = 1 CREATE TABLE #temptable ( Id INT, Url NVARCHAR(200), Title NVARCHAR(300), Address NVARCHAR(200), Domain Tinyint, Price Real, Image NVARCHAR(150), Name NVARCHAR(80), Value NVARCHAR(150) ) INSERT INTO #temptable SELECT [t].[Id], [t].[Url], [t].[GeneratedTitle] AS[Title], [t].[Address], [t].[Domain], [t].[Price], (SELECT TOP(1) ISNULL([m].[Resize1200x1200], [m].Resize730x532) FROM [Multimedia] AS[m] WHERE [t].[Id] = [m].[PropertyId] and m.MultimediaType = 1 ORDER BY [m].[Order]) AS[Image], [t].[Name], [t].[Value] FROM (SELECT [p].[Id], [p].[Url], [p].[GeneratedTitle], [g].[Address], [p].[Domain], [pr].[Amount] AS Price, [p].[ModificationDate], [f].[Name], [pf].[Value] FROM [Property] AS [p] INNER JOIN [GeoLocation] AS[g] ON [p].[Id] = [g].[PropertyId] INNER JOIN [PropertyFeature] AS[pf] ON [pf].[PropertyId] = [p].[Id] INNER JOIN [Feature] AS[f] ON [pf].[FeatureId] = [f].[Id] INNER JOIN [Operation] AS [o] ON [p].[Id] = [o].[PropertyId] INNER JOIN [OperationType] AS [o0] ON [o].[OperationTypeId] = [o0].[Id] INNER JOIN [Price] AS [pr] ON [pr].[OperationId] = [o].[Id] WHERE p.Id in (Select Id from @properties) GROUP BY [p].[Id], [p].[Url], [p].[GeneratedTitle], [g].[Address], [p].[Domain], [pr].[Amount], [p].[ModificationDate], [f].[Name], [pf].[Value]) AS[t] ORDER BY[t].[ModificationDate] DESC SET @cols = STUFF( ( SELECT DISTINCT ','+QUOTENAME(c.[Name]) FROM @features c FOR XML PATH(''), TYPE ).value('.', 'nvarchar(max)'), 1, 1, ''); SET @query = 'SELECT [Id], [Url], [Title], [Address], [Domain], [Price], [Image], ' + @cols + ' FROM (SELECT [Id], [Url], [Title], [Address], [Domain], [Price], [Image], [Value] AS [value], [Name] AS[name] FROM #temptable)x PIVOT(max(value) for name in ('+@cols+')) p'; EXECUTE(@query); DROP TABLE #temptable The execution plan and Live query statistics show me the following:
Query Execution Plan
The previous query tries to obtain randomly a X number of records IDs, holding all the filter criteria to obtain only the IDs of the records which meet that criteria. The time right now is up to 15 seconds. It’s a lot if we talk about more than 400 users using concurrently the application.
Please, help me with this. I’m three weeks trying to solve this problem with no success, but a lot of advances has been made (before it was consuming 2 minutes in avg).
If it helps, I can give you access to a “dummy” deployed version of the DB with the same quantity of records to test and see directly the problem.
Thanks in advance…
===================================================================================================== INDEXES:
the indexes that are currently on the tables are the following:
GO CREATE UNIQUE NONCLUSTERED INDEX IX_Property_ModificationDate ON [dbo].[Property] (ModificationDate DESC) WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON) GO CREATE NONCLUSTERED INDEX [IX_Property_ParentId_StatusCode] ON [dbo].[Property] ([ParentId] ASC, [StatusCode] ASC) WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON); GO CREATE NONCLUSTERED INDEX [IX_Property_ParentId_StatusCode_Id_ModificationDate] ON [dbo].[Property] ([ParentId] ASC, [StatusCode] ASC, [Id] ASC, [ModificationDate] ASC) WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON); GO CREATE NONCLUSTERED INDEX [IX_Property_ParentId] ON [dbo].[Property]([ParentId] ASC) WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON); GO CREATE NONCLUSTERED INDEX [IX_Property_Identity_Domain_StatusCode] ON [dbo].[Property]([Identity] ASC, [Domain] ASC, [StatusCode] ASC) WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON); GO CREATE NONCLUSTERED INDEX [IX_Property_Id_ModificationDate] ON [dbo].[Property] (Id ASC, ModificationDate ASC) WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON); GO CREATE NONCLUSTERED INDEX [IX_Property_PublisherId] ON [dbo].[Property]([PublisherId] ASC) WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON); GO CREATE NONCLUSTERED INDEX [IX_Property_RealEstateTypeId] ON [dbo].[Property]([RealEstateTypeId] ASC) WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON) GO CREATE INDEX FIX_Property_StatusCode_Online ON [dbo].[Property](StatusCode) WHERE StatusCode = 1 WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON) GO CREATE INDEX FIX_Property_StatusCode_Offline ON [dbo].[Property](StatusCode) WHERE StatusCode = 0 WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON) GO CREATE INDEX FIX_Property_Domain_Urbania ON [dbo].[Property](Domain) WHERE Domain = 1 WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON) GO CREATE INDEX FIX_Property_Domain_Adondevivir ON [dbo].[Property](Domain) WHERE Domain = 2 WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON) GO GO CREATE NONCLUSTERED INDEX [IX_GeoLocation_PropertyId_ModificationDate] ON [dbo].[GeoLocation] (PropertyId ASC, [ModificationDate] DESC) WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON); GO CREATE NONCLUSTERED INDEX [IX_GeoLocation_PropertyId_Address] ON [dbo].[GeoLocation] (PropertyId ASC, [Address] ASC) WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON); GO CREATE UNIQUE NONCLUSTERED INDEX IX_GeoLocation_ModificationDate ON [dbo].[GeoLocation] (ModificationDate DESC) WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON) GO CREATE NONCLUSTERED INDEX [IX_GeoLocation_Ubigeo] ON [dbo].[GeoLocation]([Ubigeo] ASC) WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON) GO CREATE UNIQUE NONCLUSTERED INDEX [IX_GeoLocation_PropertyId] ON [dbo].[GeoLocation]([PropertyId] ASC) WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON) GO CREATE SPATIAL INDEX SIX_GeoLocation_Coordinate ON [dbo].[GeoLocation](Coordinate) GO CREATE INDEX FIX_GeoLocation_Domain_Urbania ON [dbo].[GeoLocation](Domain) WHERE Domain = 1 WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON) GO CREATE INDEX FIX_GeoLocation_Domain_Adondevivir ON [dbo].[GeoLocation](Domain) WHERE Domain = 2 WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON) GO GO CREATE NONCLUSTERED INDEX [IX_Multimedia_PropertyId_Order] ON [dbo].[Multimedia] (PropertyId ASC, [Order] ASC) WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON); GO CREATE NONCLUSTERED INDEX [IX_Multimedia_PropertyId] ON [dbo].[Multimedia]([PropertyId] ASC) WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON); GO CREATE NONCLUSTERED INDEX [IX_Multimedia_Order] ON [dbo].[Multimedia]([Order] ASC) WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON); GO CREATE NONCLUSTERED INDEX [PK_Multimedia_Property] ON [dbo].[Multimedia]([Id] ASC, [PropertyId] ASC); GO CREATE INDEX FIX_Multimedia_MultimediaType_Image ON [dbo].[Multimedia](MultimediaType) WHERE MultimediaType = 1 WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON) GO GO CREATE NONCLUSTERED INDEX [IX_PropertyFeature_PropertyId_FeatureId] ON [dbo].[PropertyFeature] (PropertyId ASC, [FeatureId] ASC) WITH( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, FILLFACTOR = 90, ONLINE = ON); GO CREATE NONCLUSTERED INDEX [IX_PropertyFeature_FeatureId] ON [dbo].[PropertyFeature]([FeatureId] ASC); GO CREATE NONCLUSTERED INDEX [IX_PropertyFeature_PropertyId] ON [dbo].[PropertyFeature]([PropertyId] ASC); GO CREATE NONCLUSTERED INDEX [IX_PropertyFeature-FeatureId] ON [dbo].[PropertyFeature]([Id] ASC, [FeatureId] ASC); GO CREATE NONCLUSTERED INDEX [IX_PropertyFeature_Property] ON [dbo].[PropertyFeature]([Id] ASC, [PropertyId] ASC); GO CREATE NONCLUSTERED INDEX [IX_Operation_PropertyId] ON [dbo].[Operation]([PropertyId] ASC); GO CREATE NONCLUSTERED INDEX [IX_Operation_OperationTypeId] ON [dbo].[Operation]([OperationTypeId] ASC); GO CREATE NONCLUSTERED INDEX [IX_Price_OperationId] ON [dbo].[Price]([OperationId] ASC); GO CREATE NONCLUSTERED INDEX [IX_Price_Operation] ON [dbo].[Price]([Id] ASC, [OperationId] ASC); WP Query for Posts (Products) in Specific Category that has 2 Specific Tags (*AND* both tags not *OR*)
This is really confusing me.
I’ve tried numerous things but cannot get appropriate results.
Here’s what I currently have for my query:
$ args = array( 'post_type' => 'product', 'tax_query' => array( 'relation' => 'AND', array( 'taxonomy' => 'product_cat', 'field' => 'slug', 'terms' => array( 'ring' ), ), array( 'taxonomy' => 'product_tag', 'field' => 'slug', 'terms' => array( 'black', 'men' ), ), ), ); $ query = new WP_Query( $ args ); As you can see I am trying to return products in the product category ring that contain both the tags black AND men…
It appears though the product_tag terms array which includes black and men, is returning posts that include the tag black OR the tag men in the ring category.
How do I get it so that only products that include BOTH the tags in the query are returned?
Thanks so much
Why does a range query on a segment tree return at most $\lceil \log_2{N} \rceil$ nodes?
If an array $ A[1 \ldots N]$ is represented using a segment tree having sets in each interval, why does a range query $ [L\ldots R]$ returns at most $ \lceil \log_2{N} \rceil$ sets (or disjoint intervals)?
If came across this statement while reading this answer.
To quote:
Find a disjoint coverage of the query range using the standard segment tree query procedure. We get $ O(\log n)$ disjoint nodes, the union of whose multisets is exactly the multiset of values in the query range. Let’s call those multisets $ s_1, \dots, s_m$ (with $ m \le \lceil \log_2 n \rceil$ ).
I tried searching for a proof, but couldn’t find it on any site. Can anyone help me prove it?
MySQL – Year Over Year – Query Syntax
Experts
I am working on a query to get Year over Year data comparison from current year to prior year.
For example, if current year is Jan 1st – now, I would calc that dynamically in my query but also bring in the previous year data for the same time frame (does that make sense?).
Here is my projection query that brings in current year and prior year but the data is dynamic yet static if that makes sense. CY is focusing on all of YTD while PY is doing entire PY.
How would I match PY to mimic what CY is doing essentially giving me all the data that I am needing from PY to do the comparison.
I would appreciate your help in advance.
current_4wk_sales.store_no as 'store_no', current_4wk_sales.store_name as 'store_name', current_4wk_sales.zee_name as 'franchisee', current_4wk_sales.marketing_source as 'region', current_4wk_sales.cy_projections as 'cy_projections', prev_wk_sales.prev_year_sales as 'prev_year_sales', round(((current_4wk_sales.cy_projections - prev_wk_sales.prev_year_sales) / prev_wk_sales.prev_year_sales), 2) * 100 as 'percent_chng' from ( select store.store_uin, store.store_no, store.store_name, store.zee_name, store.marketing_source, round((((sum(ro.total_sales - ro.taxes) / 100)) / (DAYOFYEAR(current_date() - INTERVAL 2 DAY))) * 365, 2) as 'cy_projections' from cba_data_warehouse.raw_tm_data ro join cba_data_warehouse.storeinfo store on ro.store_no = store.store_no where posted_date between MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) and LAST_DAY(DATE_ADD(NOW(), INTERVAL 12-MONTH(NOW()) MONTH)) and ro.repair_order_status_id in (5,6) group by store.store_uin, store.store_no, store.store_name order by store.store_no asc ) as current_4wk_sales left join ( select store.store_uin, store.store_no, store.store_name, store.zee_name, store.marketing_source, round(sum(ro.total_sales - ro.taxes) / 100,2) as prev_year_sales from cba_data_warehouse.raw_tm_data ro join cba_data_warehouse.storeinfo store on ro.store_no = store.store_no where ro.repair_order_status_id in (5, 6) and ro.posted_date between MAKEDATE(year(now() - INTERVAL 1 YEAR),1) and DATE(CURDATE()- INTERVAL DAYOFYEAR(CURDATE()) DAY) group by store.store_uin, store.store_no, store.store_name order by store.store_no asc ) prev_wk_sales on prev_wk_sales.store_uin = current_4wk_sales.store_uin and prev_wk_sales.store_no = current_4wk_sales.store_no and prev_wk_sales.store_name = current_4wk_sales.store_name``` Thanks in advance!