The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 2,466 other followers

Archive for the ‘MySQL’ Category

@isotopp: How fast is MySQL? I was casually operating under the impression, that a typical query resolution time is ~3ms. Turns out I was off 1-2 orders of magnitude…

Posted by jpluimers on 2021/07/13

Configured properly, even across network (see the answers in the Twitter thread below).

[WayBack] Thread by @isotopp: “How fast is MySQL? I was casually operating under the impression, that a typical query resolution time is ~3ms. Turns out I was off one or e […]”

How fast is MySQL?

I was casually operating under the impression, that a typical query resolution time is ~3ms.

Turns out I was off one or even two orders of magnitude.

A “select version()” type of nonquery is handled in around 20µs.
A typical query in a point-query or BKA environment (where id = const, … where id in (c1, c2, …)) is typically handled in 350µs.

This is based on looking at

select event_name,
AVG_TIMER_WAIT/1000000 as avg_us, max_timer_wait/1000000 as max_us, min_timer_wait/1000000 as min_us from performance_schema.events_statements_summary_global_by_event_name
where event_name = ‘statement/sql/select’;

| event_name | avg_us | max_us | min_us |
+———————-+———-+———–+———+
| statement/sql/select | 336.4440 | 2218.0450 | 36.4460 |
for about any of our servers with that kind of workload that I have been looking at.
This is obviously answered mostly from the buffer pool, because if you look at storage latencies, what you see here is that MySQL is in the same ballpark as NVME or SSD storage latencies (within 2x).
TL;DR: MySQL is fucking fast, and I had no idea how to quantify fucking fast before I looked into P_S

Read the rest of this entry »

Posted in Database Development, Development, MySQL, Software Development | Leave a Comment »

Case sensitivity for SQL identifiers · ontop/ontop Wiki · GitHub

Posted by jpluimers on 2021/07/08

For my link archive: [WayBack] Case sensitivity for SQL identifiers · ontop/ontop Wiki · GitHub:

  • Oracle and H2 changes unquoted identifiers to uppercase.
    Although technically possible, Oracle explicitly recommends to not use lowercase identifers. We do not support H2 with the setting DATABASE_TO_UPPER=FALSE, if this setting is enabled all queries with names and tables in lowercase must be quoted.
  • DB2 Names are not case sensitive.
    For example, the table names CUSTOMER and Customer are the same, but object names are converted to uppercase when they are entered. If a name is enclosed in quotation marks, the name becomes case sensitive. The schema name is case-sensitive, and must be specified in uppercase characters.
  • Postgres changes unquoted identifiers (both columns and alias names) to lowercase.
  • Mysql does not change the case of unquoted tables and schemas.
    It changes in lowercase the unquoted columns. Mysql tables are stored as files in the operating system the server runs on. This means that database and table names are not case sensitive in Windows, and case sensitive in most varieties of Unix or Linux. The backtick ` is used for enclosing identifiers such as table and column names.
  • Mssqlserver All connection string property names are case-insensitive.
    For example, Password is the same as password. Identifiers of objects in a database, such as tables, views, and column names, are assigned the default collation of the database. For example, two tables with names that differ only in case can be created in a database that has case-sensitive collation, but cannot be created in a database that has case-insensitive collation. Default SQL Server is not case sensitive. SELECT * FROM SomeTable is the same as SeLeCT * frOM soMetaBLe. Delimited identifiers are enclosed in double quotation marks (“) or brackets ([]). Identifiers that comply with the rules for the format of identifiers may or may not be delimited.

–jeroen

Posted in Database Development, DB2, Development, MySQL, OracleDB, PostgreSQL, SQL Server | Leave a Comment »

showthedocs

Posted by jpluimers on 2021/02/18

[WayBack] showthedocs

is a documentation browser that finds the relevant docs for your code. It works by parsing the code and connecting parts of it to their explanation in the docs

, and supports these languages:

  • SQL
    • postgresql
    • mysql
  • Configuration
    • nginx
    • gitconfig

You can enter any language text, then click the language, followed by clicking the “SHOW ME THE DOCS!” button, for which an example is further below.

The site has an open architecture, allowing to plug in more languages and documentation:

 

gitconfig example

So for instance the below ./git/config file leads to this result [WayBack] where you can click on all the coloured areas for easy navigation through the documentation:

Read the rest of this entry »

Posted in *nix, *nix-tools, Database Development, Development, DVCS - Distributed Version Control, git, MySQL, nginx, PostgreSQL, Power User, Software Development | Leave a Comment »

Public database servers

Posted by jpluimers on 2019/10/30

I could not find any vendors/architectures have public database servers.

So there is no good way to go beyond SQLFiddle (of which I wrote before in SQL Fiddle | A tool for easy online testing and sharing of database problems and their solutions and David Rodriguez: a few nice posts on SQL (via: Google+)), that does not provide database access, but allows you to fire SQL statements onto these architectures:

  • MySQL 5.6
  • Oracle 11g R2
  • PostgreSQL 9.6
  • PostgreSQL 9.3
  • SQLite (WebSQL)
  • SQLite (SQL.js)
  • MS SQL Server 2017

I get the thing (it is very hard to secure an “over the internet” connection to a database server; do NOT do this: [WayBack] connectivity – Connect to SQL Server over Internet – Database Administrators Stack Exchange), so the alternative is to run locally.

If you run locally, there are plenty of example/demo database, like:

–jeroen

Posted in Database Development, Development, MySQL, OracleDB, PostgreSQL, SQL, SQL Server, SQLite | Leave a Comment »

SQL Fiddle | A tool for easy online testing and sharing of database problems and their solutions.

Posted by jpluimers on 2019/08/29

Via [WayBack] SQL select only rows with max value on a column, I bumped into http://sqlfiddle.com/#!9/a6c585/1:

Application for testing and sharing SQL queries.

Source: [WayBackSQL Fiddle | A tool for easy online testing and sharing of database problems and their solutions.

It is a cool site, currently supporting these SQL back-ends:

  • MySQL 5.6
  • Oracle 11g R2
  • PostgreSQL 9.6
  • PostgreSQL 9.3
  • SQLite (WebSQL)
  • SQLite (SQL.js)
  • MS SQL Server 2014

You can host it yourself using [WayBack] GitHub – zzzprojects/sqlfiddle2: New version of SQL Fiddle based on OpenIDM (in the past it was [WayBack] GitHub – zzzprojects/sqlfiddle)

Other resources for learning and playing around with SQL:

–jeroen

Posted in Database Development, Development, MySQL, OracleDB, PostgreSQL, Software Development, SQL, SQL Server | Leave a Comment »

 
%d bloggers like this: