Hacker Read

evanelias · 2023-05-11 17:55:30

Your examples regarding MySQL's features are not correct.

Need to apply an index to the result of a function? No problem, use a functional index, supported since October 2018: https://dev.mysql.com/doc/refman/8.0/en/create-index.html#cr...

Need to use a window function? No problem, supported since April 2018: https://dev.mysql.com/doc/refman/8.0/en/window-functions.htm...

reply

evanelias | karma 2211 | avg karma 2.66 · | 2019-05-09 17:09:45

> Indexing on function expression

MySQL 5.7 fully supports this. See https://dev.mysql.com/doc/refman/5.7/en/create-table-generat... and https://dev.mysql.com/doc/refman/5.7/en/create-table-seconda...

> JSON column (actually MySQL 5.6 supports it but I doubt if it's as good as Postgres)

Actually MySQL 5.6 doesn't support this, but 5.7 does, quite well: https://dev.mysql.com/doc/refman/5.7/en/json.html

reply

dwanvik | karma 1 | avg karma 0.5 · | 2018-02-12 14:03:26

Check out the new MySQL 8.0 release candidate. Is has windowing functions now.

https://dev.mysql.com/doc/refman/8.0/en/window-functions.htm...

reply

cwyers | karma 12109 | avg karma 4.27 · | 2019-07-16 11:34:40

MySQL now has window functions:

https://mysqlserverteam.com/mysql-8-0-2-introducing-window-f...

And MySQL has strict mode, which I believe is enabled by default on newer versions but available in any 5.x version:

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mo...

reply

erhaetherth | karma 305 | avg karma 1.62 · | 2023-08-22 23:05:55

MySQL has a "use index" hint. https://dev.mysql.com/doc/refman/8.0/en/index-hints.html

I may have used it at most once. But I'm not mr big data over here.

reply

wgjordan | karma 1139 | avg karma 4.47 · | 2018-06-25 00:50:03

> what if there was a different query language with explicit index syntax..

There is, it's a feature in MySQL called Index Hints [1].

[1] https://dev.mysql.com/doc/refman/8.0/en/index-hints.html

reply

Nycto | karma 709 | avg karma 6.82 · | 2011-02-11 11:37:58

MySQL supports spatial indexes exactly for circumstances like this:

http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.ht...

reply

beberlei | karma 933 | avg karma 3.44 · | 2014-04-17 15:27:15+00:00

the mysql documentation definatly states otherwise: http://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-o...

codedokode | karma 6872 | avg karma 1.97 · | 2018-12-01 21:13:00+00:00

That is a function made by MySQL developers [1]. Why don't you blame Sun or Oracle or a Swedish company whose name I don't remember for this?

[1] https://dev.mysql.com/doc/refman/8.0/en/mysql-escape-string....

reply

junon | karma 13069 | avg karma 3.09 · | 2021-11-07 13:02:12

MySQL, at least as far back as 2000, had indexes.

http://web.archive.org/web/20020610031610/http://www.mysql.c...

reply

andreyf | karma 13660 | avg karma 3.94 · | 2017-01-11 21:55:11+00:00

Also, MySQL [1]. Are you sure that you're right and all these major db projects are wrong?

1. note the default value of "bind to *": http://dev.mysql.com/doc/refman/5.7/en/server-options.html#o...

reply

duskwuff | karma 18781 | avg karma 3.23 · | 2018-12-01 17:33:34+00:00

No, that's not true. That function name was copied directly from the MySQL C API. It was not added by PHP.

https://dev.mysql.com/doc/refman/5.5/en/mysql-real-escape-st...

reply

bbarnett | karma 5933 | avg karma 1.55 · | 2021-02-27 13:18:19+00:00

https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mo...

Much of your MySQL complaint is not a MySQL issue, but a config issue.

And yes, powerful config options are good, not bad.

reply

zerg2k | karma 12 | avg karma 0.75 · | 2019-11-08 21:13:15+00:00

That’s no longer true since MySQL8 https://dev.mysql.com/doc/refman/8.0/en/atomic-ddl.html

jrochkind1 | karma 26717 | avg karma 3.67 · | 2022-02-28 16:30:34

I also didn't realize MySQL supported these JSON query operators now, huh. Since MySQL 2.7.9 in 2015 apparently? Not that new!

https://dev.mysql.com/doc/refman/5.7/en/json-search-function...

reply

kbenson | karma 31158 | avg karma 2.98 · | 2019-10-19 00:43:28+00:00

User defined functions[1] in MySQL are dynamic libraries which are loaded and then you attach a function name to. A common example might be a hashing function that you want the DB to understand.[2]

That said, I bet just about every database supports something similar (and postgres' equivalent has already been provided by a sibling comment).

1: https://dev.mysql.com/doc/refman/5.5/en/create-function-udf....

2: https://www.percona.com/doc/percona-server/LATEST/management...

reply

morgo | karma 748 | avg karma 2.56 · | 2017-06-15 10:38:16+00:00

It has been able to use more than one index per table since MySQL 5.0 (2005):

https://dev.mysql.com/doc/refman/5.7/en/index-merge-optimiza...

reply

tn1 | karma 131 | avg karma 1.46 · | 2021-08-30 23:08:36

Related MySQL feature: https://dev.mysql.com/doc/refman/8.0/en/events-configuration...

bradleyfalzon | karma 22 | avg karma 2.75 · | 2014-10-23 15:18:58

I believe InnoDB does have this now in MySQL 5.6: http://dev.mysql.com/doc/refman/5.6/en/innodb-fulltext-index...

okket | karma 40755 | avg karma 7.28 · | 2016-10-05 14:47:15

"Modern SQL" feature overview for MySQL + forks:

https://dveeden.github.io/modern-sql-in-mysql/

(aka "Look, window functions are coming")

reply