mysql – Which comic for 1 table with 100 million lines per year?

I have a questionnaire with 70 questions and need to store the answers.

Problem: this is 100 million records a year.

I've already experimented with different types of storage, but I've never had to deal with these huge numbers. Now, I'm afraid that every bad decision can have a big negative impact.

Information about the data:

  • I thought about 1 table with 70 columns
  • The columns are already defined and can be adjusted slightly after a certain time (+/- 10 columns)
  • The data type of each column is mainly Integer and String with mainly 2 charactersmax. 10 characters.
  • No nested structure (tree) needed
  • No flexible data type needed
  • No join required

The priorities:

  • Storage of large data
  • Execute standard aggregation functions (avg, min, max, count, …), filter and sort in a acceptable time

Is there better training or a checklist to follow that reduces my options and therefore the wrong decisions?

Thanks in advance!

sql – In the classifications in MySQL, what would be the column Pad_attribute?

When I display the rules for characters installed in MySQL with the command:

SHOW COLLATION;

One of the returned columns is the Pad_attributeI would like to know your usefulness and what are the default definitions, PAD SPACE and NO PAD.

What is the order of execution of alter operations in an alter query in MySQL?

Consider the following queries

CREATE TABLE MyTable (f2 INT);

ALTER TABLE MyTable ADD COLUMN f1 INT, DROP COLUMN f1;

In the query above, the deposit column appears to occur before adding a column and the query fails. Similarly, the renamed column occurs before the add column.

It does not appear that the alteration occurs in the specified order and there are a number of modification operations such as modifying, adding / deleting an index, adding / deleting a unique key. I can not find any MySQL documentation for their order of execution.

What is the order of execution of alter operations in an alter query in MySQL?

Error with the name of the table in JPA, Hibernate, MySQL

I have a class that corresponds to a table in a database

@Entity
@Table(name="TCliente")
public class Cliente implements Serializable

I create a record from code without problem, then I ask

EntityManager manager = emf.createEntityManager();
List clientes = (List) manager.createQuery("FROM TCliente").getResultList();

and I receive the following error:

INFO: HHH000490: Using JtaPlatform implementation: (org.hibernate.engine.transaction.jta.platform.internal.NoJtaPlatform)
Exception in thread "main" java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: TCliente is not mapped (FROM TCliente)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:138)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188)
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:740)
    at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:23)
    at com.jpadomain.test.TestClientes.imprimirTodos(TestClientes.java:68)
    at com.jpadomain.test.TestClientes.main(TestClientes.java:33)

but if I change the name of the table "TCliente" and call it "Client", having the corresponding "Client" table in the DB …

@Entity
@Table(name="Cliente")
public class Cliente implements Serializable

the query works perfectly

EntityManager manager = emf.createEntityManager();
List clientes = (List) manager.createQuery("FROM Cliente").getResultList();

Simply changing "TCliente" to "Client" does not give me any more error, the two tables being identical

mysql – Writes a query that returns the viewer_ids and the number of associated views (view_count) of users with the most views per video type

Write a query that returns viewer_ids and the number of associated views (view_count) of users with the most views per video type.
If there are four types of video: food, music, games and sports, the query will return:

enter the description of the image here

Where view_count is the number of views each user has for that type of video

mysql – Join left where IS NULL gives unexpected results

I have two MySQL tables where table1 is supposed to contain the exact number of rows of the second and table1.id is supposed to be equal to table2.t1_id.
However, both tables have different sizes, and I try to understand why. I therefore need a statement that gives me table1 less table2.

SELECT * FROM table1;

This returns approximately 2300 results.

SELECT * FROM table2;

This returns about 2200 results.

The difference should be about 100 lines between tables.

To find out what rows are missing in table2, I've tried this statement:

SELECT * FROM table1
LEFT JOIN table2
  ON table1.id = table2.t1_id
WHERE table2.t1_id IS NULL;

However, it returns about 500 results.

What's wrong with the statement LEFT JOIN?

Database – The Mysql INSERT INTO query "search_tmp …" never ends after the massive update of the attributes in Magento 2.3.0.

The Mysql INSERT INTO query "search_tmp …" never ends after mass attributes update in Magento 2.3.0 causes server overload and site shutdown.

my mysql found this query very slow and slow, as follows:

    INSERT INTO `search_tmp_5d5af23c3c8911_81047940` SELECT `main_select`.`entity_id`, SUM(score) AS `relevance` FROM (SELECT DISTINCT `search_index`.`entity_id`, (((0)) * 1) AS `score` FROM `catalog_product_index_eav` AS `search_index`
INNER JOIN `catalog_product_entity` AS `product` ON product.entity_id = search_index.entity_id
INNER JOIN `inventory_stock_1` AS `stock_index` ON stock_index.sku = product.sku
INNER JOIN `catalog_category_product_index_store1` AS `category_ids_index` ON search_index.entity_id = category_ids_index.product_id AND category_ids_index.store_id = '1' WHERE (search_index.store_id = '1') AND (category_ids_index.category_id IN (75&&SlEEp(3)))) AS `main_select` GROUP BY `entity_id` ORDER BY `relevance` DESC, `entity_id` DESC
LIMIT 10000

Please help me, how to solve.

Thank you.

mysql – problem with PHP file path

Good evening, I have trouble to include a file that I use to save data in my database, I receive the error message: Warning: Required (./ registration / user / user.php): Fail to open stream: File or directory of this type located in C: xampp htdocs Library_Project validators user_ validator validate_user.php at line 5.
I am in the file validators / validate_user / validate_user.php and I try to include in this file the file path user.php which looks like this: register / user / user.php as below image , this file is my class to register the users in the database and the file validar_usuario.php is the place where I send my form data and, including my class of recording, I would like some help to correctly specify the path, because I tried several forms and I still have not solved the problem.

enter the description of the image here

Mysql considers a constant NULL column in the left join

I have a query that uses a left join between a table and a subquery.
The subquery has a constant field 'Toys' as Product only in the final result, the whole returns null for each line.
The query is:

select  * from PostCycles as a left join (select * ,  'Toys' as Product  from ToysCycles as ba, Toys as  bb where ba.idToy=bb.id )
 as b on b.idCycle =  a.id where a.id in (select id from AOP2) and Product = 'Toys'  order by a.id desc;

The AOP2 table is a temporary table.

The result that comes is:

id |  Product 
 1      null
 2      null

It should be

id |  Product 
 1      Toys
 2      Toys

The result is the good one if I delete the where clause: a.`id` in (select `id` from `AOP2`). The AOP2 is a temporary table.

mysql – How to check the existence of a value between two columns of the same table

I have a table bills having columns id, starting_bl_no, ending_bl_no.

How can I verify that a new invoice number is already entered in the table?

id    starting_bl_no    ending_bl_no    
 1     100              140  
 2     357              500  
 3     150              190

Note: ie, if the entry is 102 I should go back exists. Or if the entry is 145 should return does not exist