Tag Info

Hot answers tagged

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 ...


8

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 ...


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), ...


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

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

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


5

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 define always 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 ...


5

For SQL Server we have the option of the FORCE ORDER hint. The only comparable I'm aware of for MySQL is STRAIGHT_JOIN. STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order. However, I'm inclined to ...


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. ...


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

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 ...


4

In MySQL, subselects within the IN clause are re-executed for every row in the outer query, thus creating O(n^2). The short story is, don't use IN (SELECT).


4

In Query 3, you are basically executing a subquery for every row of mybigtable against itself. To avoid this, you need to make two major changes: MAJOR CHANGE #1 : Refactor the Query Here is your original query Select count(*) as total from mybigtable where account_id=123 and email IN (select distinct email from mybigtable where account_id=345) You ...


4

As far as I know, the only way you can dynamically put something in FROM is to use Prepared Statements. Also, I believe you should use UNION to get results you want, not cross join. For instance, you result query should look like SELECT * FROM ( select bar_id, bar_weight from table1 UNION select bar_id, bar_weight from table2 )a WHERE ... ORDER ...


4

Refactor the query as follows: SELECT readings.* FROM ( SELECT boxsn FROM readings WHERE (time >= 1325404800) AND (time < 1326317400) ORDER BY `time` ASC ) readings_keys LEFT JOIN ( SELECT id AS boxsn FROM boards WHERE siteId = '1' ) boards USING (boxsn) LEFT JOIN readings ...


4

MySQL current 5.1 and 5.5 versions do not cache subqueries. Only whole queries. Subqueries are not processed as a separate item and the execution planned created is for the whole query. MariaDB (a MySQL fork), version 5.3 has an optimization feature that does exactly that: Subquery cache. If I am not wrong a similar feature will be incorporated in MySQL ...


4

a company may have both an inactive and an active account in M SO I assume it can be only one active and one inactive row. Here is a solution with JOINs only. If there can be multiple (active) rows, you should use EXISTS instead, as @ypercube already mentioned in his comment. SELECT R.*, M1.active FROM R JOIN M AS M1 ON M1.company = R.company ...


4

No, they are not equivalent. If Confirmed_Attendance.rsCode is not nullable, then your second query is equivalent to this one: SELECT cs.RSCode , CustomerName , em.EmailId FROM Customer_STN cs JOIN EmployeeMaster em ON cs.RSCode = em.UserName LEFT OUTER JOIN Confirmed_Attendance ca ON cs.RSCode = ca.rsCode ...


4

I guess that using subqueries inside functions is the main performance problem. It seems that the functions are fairly simple and you could drop them and easily rewrite the query with joins only: SELECT loan.loan_number, loan.borrower_name, ... CASE WHEN loan.date_waiver_ordered IS NULL THEN 'Not Ordered' WHEN ...


4

It seems like you want the following: SELECT p.*, `e`.*, GROUP_CONCAT(`n`.`partnumber`) as `partnumbers`, GROUP_CONCAT(`o`.`oem`) as `oems` FROM `w7nwd_com_mtapp_product` AS `p` LEFT JOIN `w7nwd_com_mtapp_product_engine` as `e` ON `e`.`product_id` = `p`.`id` LEFT JOIN `w7nwd_com_mtapp_product_partnumber` AS `n` ON `p`.`id`=`n`.`product_id` LEFT ...


4

What about: with tweets(type, id) as ( select 'add' as type, tweets.id from tweets ) select row_to_json(row(array_agg(tweets))) from tweets; This is actually one of the really cool things about PostgreSQL, arrays, and complex types. One can aggregate them and then convert. You should get a structure which is effectively: tweets[] (i.e. an array ...


4

There are a couple of things that are important here. First, there is a logical order of processing that occurs. And, the assignment of your DE column alias in the SELECT clause occurs after the WHERE clause is processed. So, the DE alias is not valid in the WHERE clause. Note that the sub-query solution is logically processed before the outer query, so ...


4

You have two right parenthesis more than left ones and a missing alias: Select * from wh_acct a -- alias added where a.rundate = (select max(a2.rundate) from wh_acct a2 -- parenthesis removed WHERE a2.ORGA = a.ORGA) UNION Select * from wh_acct a ...


4

Yes, your query will update all rows in posts. You have to include a condition that restricts the update to one row. One way to do this: UPDATE Posts SET Content = @Content WHERE Id = (SELECT TOP 1 Id FROM Posts WHERE FkThreadId = @Id ORDER BY CreationTime ASC) ; In SQL-Server, you can't update two tables in one statement, so you ...



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