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


3

If I understand correctly, I think this should work... We're checking that TableA.id+100 is in TableC.eid and then counting the number of times TableA.id+100 appears as TableB.char. It's always good to create a SQLFiddle though so that others can easily pick up your schema and code to debug. SELECT a.id, a.name, a.comment, COUNT(b.char) AS count ...


2

Speaking of style, you can improve in several places: In addition to what Craig already wrote. It's inconsistent to have one condition that only involves the doctor table in the JOIN clause, while the other one is in the WHERE clause. Be consistent, both or none. Best to put these in the WHERE clause, while the condition that links both tables goes into ...


2

None of those parentheses are necessary. Efficiency is identical for an inner join (so long as you're under join_collapse_limit). For join-lists bigger than PostgreSQL's join-reordering limit the extra terms in the ON predicate may be faster though. Focus on style. Is it logically a part of the condition that joins one table to another? Put it in the ON ...


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


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

you can do that but it needs some trickery. imagine you got this: select ... from a, b, c, d, e ... this type of query is always reordered. but if you do ... select ... from a JOIN b ... JOIN c JOIN d JOIN c ... then PostgreSQL will only reorder join_collapse_limit tables. you can reduce this variable to a low value to force PostgreSQL into your order. ...


1

I am sure that you can improve performance using indices on foreign keys i.e put an index on the accountholderid when you use it as foreign key. It's the place where you would usually put them. And you might put index on LastModificationTImestamp with desc order CREATE INDEX accountholder_LastModificationTimestamp_idx ON ...


1

SELECT * FROM table1 INNER JOIN table2 ON table1.id = '123' AND table1.id = table2.col; or SELECT * FROM (SELECT * FROM table1 WHERE id = '123') A INNER JOIN (SELECT * FROM table2 WHERE col = '123') B ON B.col = A.id;


1

The ref isn't referring to a constant, it's comparing two columns from two tables. Using the = operator will result in the ref type for table2.col = table1.id. For a query this simple, it's hard to recommend any improvements without knowing data sizes, indexes, both table structures, etc. I would say that the SELECT * isn't generally advisable unless you ...



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