summaryrefslogtreecommitdiff
path: root/doc/development/sql.md
diff options
context:
space:
mode:
authorNick Thomas <nick@gitlab.com>2019-08-12 11:29:10 +0100
committerNick Thomas <nick@gitlab.com>2019-08-16 14:05:08 +0100
commit5a574883f95373e13f663568eb4710c9d69d00d6 (patch)
tree335615cd6198f26c5100a9430b7172b7f4f8a063 /doc/development/sql.md
parent95075fee788495c76bad9b8b207111b26f12e216 (diff)
downloadgitlab-ce-5a574883f95373e13f663568eb4710c9d69d00d6.tar.gz
Remove MySQL references from development docs
I noticed the doc/development/testing_guide/best_practices.md still referenced the `[run mysql]` tags, etc. They no longer work, so I removed them, then realised I had better clean up the rest of doc/development !
Diffstat (limited to 'doc/development/sql.md')
-rw-r--r--doc/development/sql.md30
1 files changed, 9 insertions, 21 deletions
diff --git a/doc/development/sql.md b/doc/development/sql.md
index a256fd46c09..2584dcfb4ca 100644
--- a/doc/development/sql.md
+++ b/doc/development/sql.md
@@ -15,14 +15,11 @@ FROM issues
WHERE title LIKE 'WIP:%';
```
-On PostgreSQL the `LIKE` statement is case-sensitive. On MySQL this depends on
-the case-sensitivity of the collation, which is usually case-insensitive. To
-perform a case-insensitive `LIKE` on PostgreSQL you have to use `ILIKE` instead.
-This statement in turn isn't supported on MySQL.
+On PostgreSQL the `LIKE` statement is case-sensitive. To perform a case-insensitive
+`LIKE` you have to use `ILIKE` instead.
-To work around this problem you should write `LIKE` queries using Arel instead
-of raw SQL fragments as Arel automatically uses `ILIKE` on PostgreSQL and `LIKE`
-on MySQL. This means that instead of this:
+To handle this automatically you should use `LIKE` queries using Arel instead
+of raw SQL fragments, as Arel automatically uses `ILIKE` on PostgreSQL.
```ruby
Issue.where('title LIKE ?', 'WIP:%')
@@ -45,7 +42,7 @@ table = Issue.arel_table
Issue.where(table[:title].matches('WIP:%').or(table[:foo].matches('WIP:%')))
```
-For PostgreSQL this produces:
+On PostgreSQL, this produces:
```sql
SELECT *
@@ -53,18 +50,10 @@ FROM issues
WHERE (title ILIKE 'WIP:%' OR foo ILIKE 'WIP:%')
```
-In turn for MySQL this produces:
-
-```sql
-SELECT *
-FROM issues
-WHERE (title LIKE 'WIP:%' OR foo LIKE 'WIP:%')
-```
-
## LIKE & Indexes
-Neither PostgreSQL nor MySQL use any indexes when using `LIKE` / `ILIKE` with a
-wildcard at the start. For example, this will not use any indexes:
+PostgreSQL won't use any indexes when using `LIKE` / `ILIKE` with a wildcard at
+the start. For example, this will not use any indexes:
```sql
SELECT *
@@ -75,9 +64,8 @@ WHERE title ILIKE '%WIP:%';
Because the value for `ILIKE` starts with a wildcard the database is not able to
use an index as it doesn't know where to start scanning the indexes.
-MySQL provides no known solution to this problem. Luckily PostgreSQL _does_
-provide a solution: trigram GIN indexes. These indexes can be created as
-follows:
+Luckily, PostgreSQL _does_ provide a solution: trigram GIN indexes. These
+indexes can be created as follows:
```sql
CREATE INDEX [CONCURRENTLY] index_name_here