0

What would a good index be for these 2 cases?

A)

SELECT p.content, u.name
FROM posts p, admins a, users u
WHERE
p.tag = a.tag and 
a.id = u.id and 
p.content = 'Hello World';

B)

SELECT u.name, p.date
FROM users u, posts p
WHERE
u.userid = p.userid and 
p.content = 'Hello World';

I tried finding out how the performance is affected by creating different indexes one-by-one. With every new index, the query run faster, but then after dropping them, the gained performance remained. So in the end, I couldn't compare.

Gouz
  • 336
  • 1
  • 6
  • 19
  • 1
    Tip of today: switch to modern, explicit `JOIN` syntax - easier to write (without errors), easier to read, and easier to convert to outer join if needed. – jarlh Jan 24 '18 at 11:10
  • 1
    Databases cache data and statistics, etc, to memory. This may be useful to you : https://stackoverflow.com/questions/1216660/see-and-clear-postgres-caches-buffers – MatBailie Jan 24 '18 at 11:11
  • 1
    To back up @jarlh : `JOIN` notation is now over 25 years old, it's old enough to vote, drive, even get married, etc. Not to mention that the older `,` based notation is deprecated in most RDBMS. – MatBailie Jan 24 '18 at 11:14
  • @MatBailie: as much as I agree with you, I don't think the old implicit joins in the where clause are deprecated in any DBMS. In fact, they _have_ to support them because it's still part of the SQL standard. Some DBMS have deprecated their non-standard syntax for _outer_ joins in the where clause though –  Jan 24 '18 at 12:02
  • @a_horse_with_no_name : If being in the ANSI SQL Standards meant that an RDBMS implemented something, life would be a lot simpler. There is nothing that correlates `still part of the SQL standard` with `have to support them` ;) – MatBailie Jan 24 '18 at 12:11

1 Answers1

0

Never use commas in the FROM clause. Always use proper, explicit JOIN syntax.

So, write the queries like this:

SELECT p.content, u.name
FROM posts p JOIN
     admins a
     ON p.tag = a.tag JOIN
     users u
     ON a.id = u.id
WHERE p.content = 'Hello World';

And:

SELECT u.name, p.date
FROM posts p JOIN
     users u
     ON u.userid = p.userid
WHERE p.content = 'Hello World';

The best indexes are on

  • posts(content, tag)
  • admins(tag, id)
  • users(id, name)
  • posts(content, userid, date)
  • users(userid, name)

Unfortunately, there is not a single index for each table that matches these queries. They are using different columns.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • The order of the columns within these indices plays a significant role in this case? Also, assuming that the "WHERE p.content = 'Hello World';" was always fixed, wouldn't it be even better to use it somehow for partial indexing? – Gouz Jan 24 '18 at 11:58
  • @Gouz . . . The order of columns in an index is very important. – Gordon Linoff Jan 24 '18 at 12:58
  • Would different (relatively) table sizes, prefer Multi-Column Indexes of different column order? For example in case A: Should the indexing order remain the same among the following 2 cases?: 1) I expect users – Gouz Jan 24 '18 at 21:47
  • @Gouz . . . Under most circumstances, these would be the best indexes for the query, because the index would be used directly for the `where` clause. – Gordon Linoff Jan 25 '18 at 03:08