select slow because of unused inner join

I have two tables:

CREATE TABLE `A` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
CREATE TABLE `B` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a_id` int(11) NOT NULL,
  `c_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IX_a_id` (`a_id`),
  KEY `IX_c_id` (`c_id`),
  CONSTRAINT `a_id_ibfk_1` FOREIGN KEY (`a_id`) REFERENCES `A` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

They have a couple million rows each.

explain select count(*) FROM B inner join A on B.a_id = A.id WHERE B.c_id = 7;
+----+-------------+-------+--------+-----------------------+------------+---------+--------------------+--------+-------------+
| id | select_type | table | type   | possible_keys         | key        | key_len | ref                | rows   | Extra       |
+----+-------------+-------+--------+-----------------------+------------+---------+--------------------+--------+-------------+
|  1 | SIMPLE      | B     | ref    | IX_a_id,IX_c_id       | IX_c_id    | 4       | const              | 116624 | Using where |
|  1 | SIMPLE      | A     | eq_ref | PRIMARY               | PRIMARY    | 4       | test1.B.a_id       |      1 | Using index |
+----+-------------+-------+--------+-----------------------+------------+---------+--------------------+--------+-------------+

Now, I can't understand why mysql is unable to ignore the un-needed inner join to A which kills performance. i.e., the following query is equivalent to the above:

select count(*) from B where B.c_id = 7

which should be easy to infer since B.a_id can't be null and B.a_id has a constraint to the unique key A.id

Is there a way to make mysql understand this ?

MySL tables are not UTF8 even though I instructed Hibernate to do create them with UTF8

I'm using a Spring Boot application and even though I instructed h]Hibernate to make new tables in UTF8, all new tables are in latin1.

spring.jpa.properties.hibernate.connection.characterEncoding=utf-8
spring.jpa.properties.hibernate.connection.CharSet=utf-8
spring.jpa.properties.hibernate.connection.useUnicode=true

I'm using Hikari.

insert multiple values in mysql in a nested select

I'm fairly new to sql. This might be basic. I have two tables one with groups and one with members, I want to link them up so that a third table contains id_group and id_member. The value MYGROUP is supplied during the import. I tried this:

insert ignore into member_group (id_group, id_member) 
values ( ( select id_group from group where group_name='MYGROUP' ) ,
( select id_member from member ) );

But I end up with one row in member_group containing a null value.

on it's own this yields 1 for example:

select id_group from group where group_name='MYGROUP';
+----------+
| id_group |
+----------+
|        1 |
+----------+

on it's own this yields a list of id_members

mysql> select id_member from member;
+-----------+
| id_member |
+-----------+
|       123 |
|       456 |
|       789 |

I want member_group to then look like this

+-----------+----------+
| id_group  |id_member |
+-----------+----------+
|         1 |      123 |
|         1 |      456 |
|         1 |      789 |

How can I do this (without resorting to shell scripts, for loops and sed) ?

Bandwidth vs database querying

I display images that I want the user to be able to delete. I do this with an AJAX request.

Is it best practise to:

1. Pass just the ID of the image, use mysql to find what the filename of that record is, and then delete the file?

OR

2. Pass the ID and the src of the image so I can just delete the image right away without looking it up?

One uses more bandwidth, the other more processing power.

Either way I need the ID because I have to do delete the record.

How to populate javafx TableView column with values from MySQL generated column?

I am using generated columns introduced in MySQL version 5.7. How to use values from those columns to populate a javafx TableView? Excerpts from the code which I am using to populate the TableView:

public void processQuery(){
    try {
        int i = (Integer)aList.get(arrayObjectNumber);
        System.out.println(i);
        String aQuery = "select * from sale_invoice_details "
                        + "where sale_invoice_id = "+  i;
        rs = con.createStatement()
                .executeQuery(aQuery);
        System.out.println("executed aQuery");
        while(rs.next()) {
            System.out.println(rs);
        sale_invoice_details_array
                 .add(new s_i_d
                        (rs.getInt("id"), 
                        rs.getString("name"), 
                        rs.getFloat("quantity"),
                        rs.getFloat("amount_generated")));}
        processQueryB(i);
         } catch (Exception e) {
        System.out.println(e);
    }
}
  ...

public class s_i_d {
    private IntegerProperty id;
    private StringProperty name;
    private FloatProperty quantity;
    private FloatProperty amount_generated;
    private s_i_d(Integer id, String name, Float quantity, Float amount_generated) {
        this.id = new SimpleIntegerProperty(id);
        this.name = new SimpleStringProperty(name);
        this.quantity = new SimpleFloatProperty(quantity);
        this.amount_generated = new SimpleFloatProperty(amount_generated);
    }
    public IntegerProperty idProperty() {return id;}
    public StringProperty nameProperty() {return name;}
    public FloatProperty quantityProperty() {return quantity;}
    public FloatProperty amount_generatedProperty() {return amount_generated;}
}

In the console:

    ...
   executed aQuery
   com.mysql.jdbc.JDBC4ResultSet@248423af
   java.sql.SQLException: Column 'amount_generated' not found.
    ...

MySQL table code:

CREATE TABLE `sale_invoice_details` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `rate` float DEFAULT NULL,
  `quantity` float DEFAULT NULL,
  `discount` float DEFAULT NULL,
  `amount` float DEFAULT NULL,
  `sale_invoice_id` int(11) DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  `amout_generated` float GENERATED ALWAYS AS (quantity*rate) VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `fk_sale_invoice_id_idx` (`sale_invoice_id`),
  KEY `fk_product_id_idx` (`product_id`),
  CONSTRAINT `fk_product_id` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_sale_invoice_id` FOREIGN KEY (`sale_invoice_id`) REFERENCES `sale_invoice` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=32303 DEFAULT CHARSET=latin1$$

How can I rebuild indexes and update stats in MySQL innoDB?

I have experience with MS SQL server where it is possible and useful to update statistic and rebuild indexes. I can't find such option in MySQL innoDB, is there such option? If not, how MySQL database create an execution plan? Does the MySQL update indexes and statistic with every UPDATE and INSERT?

Choose from various dates using date time picker

This button filters the datagridview using datetimepicker. What should I do if I want to filter it between dates?

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click

    MySqlConn = New MySqlConnection
    MySqlConn.ConnectionString = "Server = Localhost; database = venuesdb; user id = root; Password = "
    Dim SQLDataAdapter As New MySqlDataAdapter
    Dim DatabaseDatSet As New DataTable
    Dim Bindsource As New BindingSource        
    Try
        MySqlConn.Open()
        Dim Query = "Select * From venuesdb.cost where EventDate  = ('" & DateTimePicker1.Text & "')"
        Command = New MySqlCommand(Query, MySqlConn)
        SQLDataAdapter.SelectCommand = Command
        SQLDataAdapter.Fill(DatabaseDatSet)
        Bindsource.DataSource = DatabaseDatSet
        DataGridView1.DataSource = Bindsource
        SQLDataAdapter.Update(DatabaseDatSet)
        MySqlConn.Close()
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
    MySqlConn.Dispose()
End Sub