Tag Info

Hot answers tagged

5

SQL Server has an upper bound on creating efficient query plans given a moderately complex query involving a lot of joins - there isn't a single upper bound or magic formula to determine when a query is complex enough to cause a problem; it is very case-by-case and involves baselining from some known expectation (people sometimes think a certain query ...


5

What are the possible reasons of the slow table insert? What are ways to identify this bottleneck without the execution plan? Read How to analyse SQL Server performance, specially the part about Analyzing individual query execution wait times. What actions can I take to reduce the cost of the table insert? That would depend largely on the result ...


3

You need CROSS APPLY not join. The definition of table expressions involved in joins must be stable. I.e. They can't be correlated such that the table expression means something different dependant on the value of a row in another table. select f.ID, f.Desc, u.Field1, u.Field2 from Foo f Cross apply ut_FooFunc(f.ID, 1) u where f.SomeCriterion = ...


2

This would be more efficient: With json and json_array_elements() in pg 9.3 SELECT p.id AS p_id, p.data AS p_data , c.id AS c_id, c.data AS c_data FROM test p LEFT JOIN LATERAL json_array_elements(p.data->'children') pc(child) ON TRUE LEFT JOIN test c ON c.id = pc.child::text::int; Use the -> operator instead of ->> in the reference to ...


2

2nd query: Of course you get duplicate rows. 1 row per producto each fabricante is connected to - multiplied with the number of rows in pais each combination is connected to. 1st query: An explicit JOIN binds before (groups of) comma-separated items in the FROM list. This is why you cannot reference fabricante in the JOIN condition between pais and ...


1

Exclude users without emails Assuming we only want users that actually have emails. Users without emails are ignored. The reason I went with this assumption at first is that all your queries do that already: LEFT JOIN emails on users.id = emails.user_id WHERE emails.email LIKE 'a' || '%%' By adding a WHERE condition on emails.email you effectively ...


1

You need to get the maximum address_id per user_id SELECT user_id,MAX(address_id) address_id FROM users_addresses GROUP BY user_id; Make that query a subquery and join back to the other tables SELECT u.first_name , u.last_name , u.username , u.password , a.user_id , a.street , a.email , c.name FROM (SELECT user_id,MAX(address_id) address_id FROM ...


1

YOUR ORIGINAL QUERY select event_id, count(*) as num_bookmarks from event_connections where connectiontype = 3 join event_id on events.id=event_connections.event_id group by event_id; PROPER SYNTAX select event_id, count(*) as num_bookmarks from event_connections join events on events.id=event_connections.event_id where event_connections.connectiontype = ...


1

Nevermind, I found the way SELECT * FROM ( SELECT *, json_array_elements((data->>'children')::JSON) child FROM test) x1 LEFT JOIN test x2 ON x1.child::TEXT::INT = x2.id ; id | data | child | id | data ----+--------------------------------------+-------+----+----------------------------------- 1 ...


1

This is your original query SELECT DISTINCT d.movieName, d.castName, d.movieImdbId, f.year, f.posterLink FROM director_movie as d LEFT JOIN film_info as f ON d.movieImdbId = f.ImdbId WHERE d.castName LIKE '%castname%' There are three things you can do SUGGESTION #1 You should reorganize the query so that the castName is searched first SELECT DISTINCT ...


1

I think you need a UNION or an OR with EXISTS: SELECT c.* FROM change AS c WHERE c.project_id = 10 OR c.project_id IS NULL AND EXISTS ( SELECT * FROM change AS x WHERE x.project_id = 10 AND x.object_id = c.object_id ) ;


1

Using FULLTEXT indexes has to be handled with great care. Why ? While FULLTEXT index searches do work, the MySQL Query optimizer tends to suggest full table scans if you do not express the query properly. Let's take your query and look for 'tom' SELECT DISTINCT c.movieName, c.castName, c.movieImdbId, f.year, f.posterLink FROM cast_movie as c JOIN film_info ...


1

in ANSI SQL the DISTINCT is totally pointless here. UNION automatically filters duplicates: test=# SELECT 1 UNION SELECT 1; ?column? ---------- 1 (1 row) There is a sharp distinction between UNION and UNION ALL: test=# SELECT 1 UNION ALL SELECT 1; ?column? ---------- 1 1 (2 rows) in your case the second subselect can never ...


1

-- ---------------------------- -- Table structure for adresses -- ---------------------------- DROP TABLE IF EXISTS `adresses`; CREATE TABLE `adresses` ( `adress_id` int(11) NOT NULL AUTO_INCREMENT, `adress_adress` varchar(255) COLLATE utf8_unicode_ci NOT NULL, ...


1

Table layout It would be more efficient to reverse the column order in nodes: fixed length NOT NULL columns first. This is just a tiny optimization. It's only the first item because table layout comes first. CREATE TABLE nodes( id bigint PRIMARY KEY , type text NOT NULL , name text ); Index Replace substring(name,1,2700) with left(name, 2700) ...



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