Hot answers tagged

21

While I agree with other commenters that this is a computationally expensive problem, I think that there is a lot of room for improvement by tweaking the SQL that you are using. To illustrate, I create a fake data set with 15MM names and 3K phrases, ran the old approach, and ran a new approach. Full script to generate a fake data set and try out the new ...


11

As already indicated in the comments it looks as though you need to update your statistics. The estimated number of rows coming out of the join between location and testruns is hugely different between the two plans. Join plan estimates: 1 Sub query plan estimates: 8,748 The actual number of rows coming out of the join is 14,276. Of course it makes ...


10

You can solve this problem using intersect statements. Do a separate select for each tag_id and join them with intersects and you'll only get the records that match all three tag_ids. select products.id, products.name from products join tag_ties on tag_ties.ref_id = products.id where tag_ties.tag_id = 10 intersect select products.id, products.name from ...


10

But the execution plan for both is same as shown below: The plans are different. One is an inner join, the other is an outer join. The results may be the same in your simple test, but the semantics are different. In more complex queries, the difference may cause more obviously different execution plans, and come with a performance impact. There are ...


9

It's OK for nested subqueries to use the same aliases as used in the parent query, although it might be a bit confusing for someone reading the code. The name space for aliases on a nested subquery is separate from the name space on the parent. For example the query below has a nested subquery b that also has an alias b used within it. This would be ...


8

Try something like this: select t1.id, t1.name from ( select p.name as name, p.id as id from products p inner join tag_ties ties on p.id=ties.ref_id where ties.tag_id in (10,11,12) ) as t1 group by t1.id, t1.name having count(t1.id) = 3 order by t1.name asc ;


8

I have rather bad news for you in this regard MySQL Query Optimizer tends to stray away for further optimization once it sees a FULLTEXT index. I have written about this before in the StackExchange May 23, 2011 : http://stackoverflow.com/a/6092216/491757 Oct 25, 2011: FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional Jan ...


8

The subquery from the selected answer isn't needed. To select products with all the given tag ids the query can be simply: SELECT p.* FROM products AS p INNER JOIN tag_ties AS tt ON tt.ref_id = p.id AND tt.tag_id IN (10, 11, 12) GROUP BY p.id HAVING COUNT(p.id)=3 Extending this idea, we can also query based on the tag ...


8

Blowing up my comment with an example. It really doesn't matter whether you're using a GUID or any other data type. Your filter should evaluate to a true or false condition. In .net/python/java/etc that's what you get. In the SQL world, we get to have tri-state built into everything. It's true, it's false, it's... I don't know. When a NULL value enters the ...


8

Just to provide some additional explanation to billinkc's answer. If null is a trump card you might be wondering why doesn't WHERE 2 IN (2,3, NULL) exhibit the same behavior? That one works as expected because it evaluates to (2=2) OR (2=3) OR (2=NULL). Under the rules of three valued logic for Or-ed conditions if any of them evaluate to true the ...


7

If you want to use a SELECT statement where only a single value is allowed you need to put that SELECT statement into brackets: insert into dates (d) values ( (select sysdate from dual) ) That could be extended for multiple columns: insert into dates ( id, d, other_column ) values ( some_sequence.nextval, (select sysdate from dual), ...


7

Try: where exists (select * .... where Customers.orderId = ... or Customers.secondaryId = ... ) Eg, if you were planning on: where orderId in (select value from ...) or secondaryorderid in (select value from ...) Then you make it so that you only call your subquery once, and build your OR clause into it. where exists (select * ...


6

It means the number of columns you select in the top query must be the same as the number of columns in the second. If you don't have the same number, you can work around it. Below I can add a NULL to the second query because it's missing a third column. SELECT col1,col2,col3 FROM t1 UNION ALL SELECT col1,col2, NULL FROM t2


6

The feature of Postgres to be able to use the primary key of a table with GROUP BY and not need to add the other columns of that table in the GROUP BY clause is relatively new and works only for base tables. The optimizer is not (yet?) clever enough to identify primary keys for views, ctes or derived tables (as in your case). You can add the columns you ...


6

Let's shelve the obvious issue brought up by Aaron Bertrand in the comments for a second: So you are scanning the table 3K times, and potentially updating all 15MM rows all 3K times, and you expect it to be fast? The fact that your subquery uses the wild cards on both sides dramatically affects sargability. To take a quote from that blog post: ...


6

If am not wrong this is what you need select team,max(Total) from ( SELECT team, Sum (total) AS Total FROM campaign WHERE Year(time) = Year(Now()) AND Month(time) = 12 GROUP BY campaign_identi, team ) A Group by team


6

I'd probably use SELECT TOP (1) WITH TIES PolicyNumber, decpageid, Risk FROM StatRiskDecpages WHERE PolicyNumber = 'AR-0000301132-04' ORDER BY decpageid DESC Assuming the covering index on (PolicyNumber, decpageid) INCLUDE(Risk) this will give you a plan like


6

You need to change your subquery approach slightly - moving the condition from the WHERE clause to a join, to bypass MySQL limitations. If the id and creation_time always define the same ordering, you can use this: DELETE p FROM pixels AS p JOIN ( SELECT id FROM pixels ORDER BY id LIMIT 1 OFFSET 4 ...


6

(SELECT courses FROM wp_category WHERE CatID =401) OR (SELECT meta_value FROM wp_postmeta WHERE post_id IN (SELECT courses FROM wp_category WHERE CatID =401) AND meta_key ='post_id' ) This is a condition, but you need n values. This should work: ( ID IN ( (SELECT courses FROM wp_category WHERE CatID ...


6

Just out of curiosity, can you try this version? It may trick the optimizer to use the same indices that the subqueries would use separately: SELECT * FROM (SELECT l.id, l.account_id, l.vnum, l.count, l.preis, l.zeitpunkt AS zeit, 'hp' AS source FROM is_log AS l WHERE l.account_id = 730 ORDER BY l.zeitpunkt DESC LIMIT 10) AS a UNION ALL SELECT * ...


6

You don't need all the derived tables. You are joining the basic (product) too many times. You can write the query joining it only once. Compound indices are a must for EAV designs. Try adding an index on (attribute_id, product_id, value) and then the query: SELECT t0.id, t1.`value` AS length, t2.`value` AS height, t3.`value` AS ...


6

Ignoring the lessons about NULL (which you should observe), NOT IN is a horrible choice for a left anti-semi join. Try: select a.guid from dbo.tableA AS a where not exists (select 1 from tableB AS b where b.guid = a.guid) As an aside, your not in version mentioned b.guid, but I see no table aliased as b.


6

You can't reference an alias in the WHERE clause - this is just because of the order in which SQL Server parses the statement. There have been many discussions about this here and on StackOverflow. A couple of examples that give some background: Why is the SELECT clause listed first? Why are queries parsed in such a way that disallows the use of column ...


6

The elegance of the answer will vary with the DBMS of your choice. In it's simplest form: select name, city as address from temp1 union select name, phone_no as address from temp1 union select name, pincode as address from temp1 I believe this should be supported by most DBMSes. If your DBMS supports lateral (cross apply in sqlserver?) you can do ...


5

By way of an example that addresses all the items @ypercube brought up in his comments on your question, you could redesign your table structure like: CREATE TABLE Workers ( WorkerID INT NOT NULL CONSTRAINT PK_Posts PRIMARY KEY IDENTITY(1,1) , ManagedByWorkerID INT NULL CONSTRAINT FK_Workers_WorkerID REFERENCES Workers(WorkerID) , Salary ...


5

Assuming combination product_special.id, product_special.priority is unique SELECT p.*, special_price,special_date FROM product p LEFT JOIN ( SELECT ps.id, ps.price as special_price, ps.`date` as special_date FROM product_special ps INNER JOIN ( SELECT id, MIN(priority) as min_priority FROM product_special ...


5

I think this is equivalent: SELECT p.name, p.image, p.price, ps.price AS special_price, ps.date FROM product p LEFT JOIN product_special ps ON p.id = ps.id AND ps.date < NOW()


5

Execution plans (actual, not estimated) need to be added to the Q for a definitive answer but... How Can the Same Query in Two Nearly Identical Instances Generate Two Different Execution Plans? Because, by your admission, they are not identical. Most likely explanation for the different execution plans is a variance in statistics. Table rows ...


5

The core issue appears to be that the optimizer does not (or cannot) use the index idx_17109 to seek for the c.precedence IS NOT NULL predicate. The following modification allows the seek, but still requires a hint to avoid the sort: SELECT pcm.catalog_id FROM cat_catal_defer AS c USE INDEX (idx_17109) JOIN cat_produ_catal_map_defer AS pcm ON ...


5

No. But almost. There are four things that joins do. I wrote about this at: JOIN simplification in SQL Server Your "NOT IN" (which you should be careful of, regarding NULLs - try using NOT EXISTS) won't duplicate any rows. A LEFT JOIN can. But consider the use of Unique Indexes/Constraints/PKs, which can help the Query Optimizer treat these as identical. ...



Only top voted, non community-wiki answers of a minimum length are eligible