Suppose that I have these tables,and example data (the real table I am working on has more columns)

table `users`

table `region`

table `purchase`

Should I use subquery,cte or join when those tables have billions of records (so efficiency matters) and

1. query which city,state,country does the user id=1 lives (Yield 1 row)
2. query the name, city, price of a purchase order by price desc limit 100 (Get details on top 100 purchases).
3. query the number of the purchases, sum of price of users whose name begins with "Dr"
4. query the average spending(sum of price) of users in each city in country "c"

I have mysql table that holds log data for some products, this data are deleted and inserted by a service every 5 minutes.

This is the table:

| id | product_id | dev_stage_id | production_order | log | log_type | date

Right now id is the auto-increment primary key but is in fact useless as we never use it. Every other column is not unique, so we cannot have a different composite primary key.

As I said before a service delete all rows by product_id and dev_stage_id every 5 minutes and then new data is created. This means id gets very big very quickly.

The data are later used for display using a simple query like:

SELECT * FROM table where product_id = 1231 and dev_stage_id = 233

Is it ok in our case to just remove the id column and have no primary key or should we have a different structure ?

That query could be made faster with the following index:

CREATE INDEX ON worker_activity (created_at, action);

I have a table with columns having values 0 and 1 like give below

Column A = 0,1,0 Column B = 0,1,1 Column C = 1,1,1

I need to count occurrence of "1" from each column. (eg. A = 1, B = 2, C = 3). Kindly guide me to get the count based on column count.

This can use the index:

SELECT *
FROM   some_table
WHERE  expires_at
     < date_trunc('day', (now() AT TIME ZONE 'America/New_York')) AT TIME ZONE 'America/New_York'
-- ORDER BY expires_at  --!!?
LIMIT  5;

db<>fiddle https://dbfiddle.uk/?rdbms=postgres_14&fiddle=e1bd83a8d47ec406f39e500cb8d7dec0 - proofing equivalence

You may want to add ORDER BY expires_at or ORDER BY expires_at DESC to get deterministic results (and still use the index).

Wait ... what?

https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT

The function timezone(zone, timestamp) is equivalent to the SQL-conforming construct timestamp AT TIME ZONE zone.

So this is your query in a more commonly used form:

SELECT *
FROM   some_table
WHERE (expires_at        AT TIME ZONE 'America/New_York')::date
    < (CURRENT_TIMESTAMP AT TIME ZONE 'America/New_York')::date
LIMIT  5

(The cast and LIMIT are still Postgres-specific, the rest is now standard SQL.)

See:

• https://stackoverflow.com/a/13676871/939860
• https://dba.stackexchange.com/questions/63548/difference-between-now-and-current-timestamp/63549#63549

To make the index applicable, you need a "sargable" expression, i.e. Postgres must be able to place the indexed term on the left side of an applicable operator, and a stable value to the right. See:

• https://stackoverflow.com/a/29245753/939860

It may help to express your objective in plain English:
Get rows where expires_at adjusted to the time zone 'America/New_York' falls before 00:00 hours of the current day at that time zone.

This can be broken down into 4 steps:

1. Take the current timestamp with time zone:

   now()

2. Get the according local timestamp without time zone for New York:

   now() AT TIME ZONE 'America/New_York'

3. Truncate it to the start of the day (still timestamp without time zone

   date_trunc('day', (now() AT TIME ZONE 'America/New_York'))

4. Get the according timestamp with time zone:

   date_trunc('day', (now() AT TIME ZONE 'America/New_York')) AT TIME ZONE 'America/New_York'

test=> SELECT now() AS step1
test->      , now() AT TIME ZONE 'America/New_York' AS step2
test->      , date_trunc('day', (now() AT TIME ZONE 'America/New_York')) AS step3
test->      , date_trunc('day', (now() AT TIME ZONE 'America/New_York')) AT TIME ZONE 'America/New_York' AS step4;
        step1             |           step2           |        step3        |         step4          

------------------------------+---------------------------+---------------------+------------------------

2022-05-21 19:52:34.23824+02 | 2022-05-21 13:52:34.23824 | 2022-05-21 00:00:00 | 2022-05-21 06:00:00+02

(1 row)

Keep in mind that timestamptz is displayed according to the current time zone setting of your session ('Europe/Vienna' in my example), which has no bearing on the value whatsoever.

There are https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT-TABLE with text input (plus a third one for the broken timetz, which shouldn't be used): one transposing timestamp to timestamptz, and one for the reverse operation of transposing timestamptz to timestamp. My query uses both.

Likewise there are two (three) Postgres functions:

test=> SELECT proname AS func_name
test->      , pg_get_function_arguments(oid) AS arguments
test->      , pg_get_function_result (oid) AS result
test-> FROM   pg_proc
test-> WHERE  proname = 'timezone'
test-> AND    proargtypes[0] = 'text'::regtype;
func_name |             arguments             |           result

-----------+-----------------------------------+-----------------------------

timezone  | text, timestamp without time zone | timestamp with time zone

timezone  | text, timestamp with time zone    | timestamp without time zone

timezone  | text, time with time zone         | time with time zone

(3 rows)

Basics:

• https://dba.stackexchange.com/questions/203890/batch-update-timestamp-records-to-correct-timezone/203919#203919
• https://stackoverflow.com/a/9576170/939860

I have sql server 2019 enterprise on windows server 2019, with 30 databases totalling 500GB size. RAM is 100GB.

Of this 94GB is allocated as Max sql server memeory.

So 6GB remains for OS.

There is windows cluster and AG setup for 15 databases. I believe that memory for the cluster and AG are separate from the 94GB max sql memory setting. Is there any guide to estimate how much memory cluster and AG require?

So I have a disagreement with a coworker about using enums vs lookup tables.

https://komlenic.com/244/8-reasons-why-mysqls-enum-data-type-is-evil/ still gets referenced again and again by those not liking enums.

But we are using MySQL8.0, on databases that are not that big, like 50 to 60 tables, with 30ish enums columns.
A few tables have up to a couple millions rows, most are way below 500K rows.
Almost all enums have fewer than 10 values, and only very few have changed (once) in their life, always to add a new value at the end of the possible values.
And we never "suffered" from any disadvantages referenced by the article like storing metadata alongside it, referencing the same values from other tables, etc...

Despite what the article says at point 2., today, adding a values at the end of the list doesn't update the whole table, but removing a value always update (or at least read) all rows.

I do understand there are two schools of though and I understand the advantages and disadvantages of both.

But is there, today on MySQL8, any actual/factual/technological reasons so **switch away from enums for the existing columns and justify all the work in both the app and db required to do so ?

Thanks for reading and your answers.

I'm trying to learn about indexes in Oracle 18c.

As a novice, it's occurred to me that it might be interesting to view the actual data that's in a function-based index:

create index sidewalk_endpoint_list_idx on my_owner.sidewalks(my_owner.endpoint_list(shape),objectid); 

Is there a way to SELECT an index's data in a query?

Fake example:

select
    *
from
    sidewalk_endpoint_list_idx

That might be a silly question. Maybe indexes aren't stored in tables. But I thought it was worth asking, just so I can learn.

I've checked https://dev.mysql.com/doc/refman/8.0/en/information-schema-tables-table.html , but couldn't find anything that could explain this.

mysql> SELECT * FROM information_schema.`TABLES` WHERE table_schema='myschema' AND TABLE_NAME ='mytable' \G;
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: myschema
     TABLE_NAME: mytable
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 262354
 AVG_ROW_LENGTH: 114
    DATA_LENGTH: 30015488
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 4194304
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2022-05-17 12:00:32
    UPDATE_TIME: 2022-05-17 06:00:37
     CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_0900_ai_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: 
1 row in set (0,04 sec)

As you can see, it looks like the table was created after it was updated. Any idea what can cause this?

EDIT: Here's the schema:

show create table myschema.mytable \G;
| mytable | CREATE TABLE mytable (

app_id char(27) NOT NULL,

ref_id int unsigned NOT NULL,

value decimal(10,5) unsigned NOT NULL,

created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,

updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (app_id,ref_id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

Multiple tables and is bad for maintenance and code and MySQL. You should not be touching .ibd files in the file system. There is nothing you can do with the files that would not risk damaging the data.

Furthermore having one-table-per-week (or whatever) is a bad schema design. That discussion has been had repeatedly on this forum as well as StackOverflow.

The alternative is PARTITION BY RANGE (TO_DAYS(...)) and explicitly (say, weekly) DROP (if you need to purge) and REORGANIZE to create a new partition.

Perhaps the only advantage of using PARTITIONing for a time-series is the efficiency of deleting old data via DROP PARTITION. I discuss this, and more, in http://mysql.rjweb.org/doc.php/partitionmaint

OK, for your desire to efficiently migrate a week's data to somewhere else, see "transportable tablespaces" for turning a PARTITION into a TABLE, and then moving it to another machine. There, you could re-hook it up to a partitioned table.