Fastest Way To Execute a Query From 15,000,000+ rows

See also questions close to this topic

• Why does this Sql query shows the possible changes but does not implement it?

  So, I want to change the prefix of my tables and the following command shows the possible changes that will take place which seems alright but does not seem to implement it.

  SELECT Concat('RENAME TABLE ', TABLE_NAME, ' TO fan_', SUBSTRING_INDEX(TABLE_NAME, 'pc_',-1), ';') FROM information_schema.tables WHERE table_name like 'pc_%' and table_schema='testdbhere'

  Moreover, this isn't a writing privilege issue as changing the tables name individually works perfectly from the same user.
• how to get weeklytotal and yesterday record in mysql in one table

  Hi Everyone i am trying to implement query to get weekly and yesterday data in same table, dummy output i have shared below, if yesterday not exist as per employee_id it should we showing 0 also as per my table week start from monday and end at sunday.please help me out how to query this get weekly_total and yesterday record and one table.

  Table name-dailydata-

  Sample data

  employee_id	date	total
  20	2022-04-25	10
  20	2022-04-26	20
  20	2022-04-27	20
  20	2022-04-28	10
  20	2022-04-29	20
  20	2022-04-30	30
  20	2022-04-31	40
  20	2022-05-01	50
  40	2022-04-26	20

  expected output

  employee_id	weekly_total	yesterday_record
  20	200	40
  40	20	0

  mysql query to get weekly data

  select employee_id,sum(total) as week_total from dailydata where date between '2022-04-25' and '2022-05-01'
  
• Join to another table only matching specific records

  I have a table of ports:

  drop table if exists ports;
  create table ports(id int, name char(20));
  
  insert into ports (id, name ) values
    (1, 'Port hedland'), 
    (2, 'Kwinana');
  

  And a table of tariffs connected to those ports:

  drop table if exists tariffs;
  create table tariffs(id int, portId int, price decimal(12,2), expiry bigint(11));
  
  insert into tariffs (id, portId, price, expiry ) values
      (1, 2, 11.00, 1648408400), 
      (2, 2, 12.00, 1648508400), 
      (3, 2, 13.00, 1648594800), 
      (4, 2, 14.00, 1651273200),
      (5, 2, 15.00, 2250000000 );
  
  insert into tariffs (id, portId, price, expiry ) values
      (1, 1, 21.00, 1648408400), 
      (2, 1, 22.00, 1648508400), 
      (3, 1, 23.00, 1648594800), 
      (4, 1, 24.00, 1651273200),
      (5, 1, 25.00, 2250000000 );
  

  Each tariff has an expiry.

  I can easily make a query to figure out the right tariff for as specific date for each port. For example at timestamp 1648594700 the right tariff is:

  SELECT * FROM tariffs 
  WHERE 1648594700 < expiry AND portId = 2
  ORDER BY expiry
  LIMIT 1
  

  Result:

  id  portId  price   expiry
  3   2   13.00   1648594800
  

  However, in my application I want to be able to pull in the right tariff starting from the ports record.

  For one record, I can do this:

  SELECT * FROM ports 
  LEFT JOIN tariffs on tariffs.portId = ports.id 
  WHERE 1648594700 < tariffs.expiry AND ports.id = 2
  LIMIT 1
  

  Result:

  id  name    id  portId  price   expiry
  2   Kwinana 3   2   13.00   1648594800
  

  This feels a little 'dirty', especially because I am doing a lookup on a record, and then forcing only one result using LIMIT. But, OK.

  What I cannot do, and can't work out how to do, is a query that will return a list of ports, and each port having a price field that matches the constraint above (that is, the record with the highest expiry compared to 1648594700 for each port).

  This obviously won't work:

  SELECT * FROM ports 
  left join tariffs on tariffs.portId = ports.id
  where 1648594700 < tariffs.expiry
  

  Since the result of the query, testing with timestamp 1648594700, would be:

  id  name    id  portId  price   expiry
  2   Kwinana 3   2   13.00   1648594800
  2   Kwinana 4   2   14.00   1651273200
  2   Kwinana 5   2   15.00   2250000000
  1   Port he 3   1   23.00   1648594800
  1   Port he 4   1   24.00   1651273200
  1   Port he 5   1   25.00   2250000000
  

  Instead, the result for all ports (before further filtering) should be:

  id  name    id  portId  price   expiry
  2   Kwinana 3   2   13.00   1648594800
  1   Port he 3   1   23.00   1648594800
  

  Is there a clean, non-hacky way to have such a result? As an added constraint, is this possible for this to be done in ONE query, without temp tables etc.?
• Procedure to display the Employees of a specific Department

  Create a stored procedure, to display the lastnames as "Name" from the employee table. The requirement is as below: The name of the Procedure : EmployeesDept

  Name of the argument as Input : DeptNo

  Write the code to create the procedure

  Schema

  I wrote the Query like this but i didnt get expected output

  CREATE  PROCEDURE Employeesdept(@Deptno varchar)
  AS
  Begin
  SELECT lastname as name
  from employee
  where workdept= 'D21'
  end
  

  Expected output:

  Name --------------- Pulaski Jefferson Marino Smith Johnson Perez Monteverde Name ---------------
• C# MariaDB Update Function working in DataStore but not updating in .NET Application

  My initial issue is that my database does not update when I call on this specific code. I am not sure if it is C# itself or the update query that I am calling.

  string _connectionString = "validConnectionstring";
  
  using (MySqlConnection _mySqlConnection = new MySqlConnection(_connectionString)
  {
  _mySqlConnection.Open();
  using (MySqlCommand command = new MySqlCommand("UpdateProfileStatus", _mySqlConnection))
  {
      command.Transaction = _mySqlConnection.BeginTransaction();
      command.CommandTimeout = TimeSpan.FromSeconds(60).Seconds;
      command.CommandType = CommandType.StoredProcedure;
      command.Parameters.AddWithValue("@_username", "test");
      command.Parameters.AddWithValue("@_status", true);
      command.ExecuteNonQuery();
      
  }
  _mySqlConnection.Close();
  }
  
  

  I get no updates in my database but my console logs that the query is executed returning the value of 1 but there is no update that actually happens in my DB. Is there something in my code to reason why it is failing?

  Here is the stored procedure that I have for the update command

  CREATE PROCEDURE UpdateProfileStatus(_username VARCHAR(25), _status BOOL)
  UPDATE Profile SET status = _status WHERE username = _username;
  
  
  

  I know the Stored Procedure works but am not sure why my .NET application is not responding to my procedure call. Is it something to do with my implementation of the parameters or is it my procedure itself?

  Image of DataStore Setting within MariaDB
• Sequence number, incorrect number

  In a spring boot application, I have this entity

  @Data @NoArgsConstructor @AllArgsConstructor @Entity public class User {

  @Id
  @GeneratedValue(generator="user_id_seq")
  @SequenceGenerator(name="user_id_seq",sequenceName="user_id_seq", allocationSize=1)
  Long id
   ...
  

  }

  In mariadb, when I check the sequence

  CREATE OR REPLACE SEQUENCE `user_id_seq` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB
  

  In db, I have only 5 user.

  select id from `user` u 
  

  This query return

  1002 1004 1005 2007 3001

  Why it's not 1, 2, 3, 4, 5

  Is it beaucause of the cache 1000?