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


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


9

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


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


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

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

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


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

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


5

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


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

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

Here's how I refactored your query. (first I'll post your query with your execution plan, and then follows is my new query and the corresponding query plan) Your Query Select t1.PolicyNumber, t2.DecpageID, t2.Risk from ( select PolicyNumber, MAX(decpageid) as decpageid from StatRiskDecpages where PolicyNumber ...


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

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

SELECT A.* FROM default_relations_users A LEFT JOIN default_relations_users B ON A.id_user_rq = B.id_user_ap AND A.id_user_ap = B.id_user_rq WHERE B.id_user_rq IS NULL; I loaded you sample data (I added an additional index) mysql> use Reynierpm Database changed mysql> DROP TABLE IF EXISTS `default_relations_users`; Query OK, 0 rows affected (0.00 ...


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

That's what GROUP BY is used for. Get one row (per group). In this case, it will show all distinct user_id values and for the rest of the columns, you can (have to) use aggregate functions like MIN(), MAX(), AVG(), SUM() as you will have more than one values per group and only one can be shown. SELECT user_id , MIN(comment) AS comment -- it will show ...



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