Search code examples
c#mysqlsentence

Optimization database

I have a question about how to optimize my database :

I have a first table, where I set a list of elements (plates) with all its geometric definitions, that looks like that :

CONTRACT | NAME | CONTOURS |  HOLES  | SCRIBING
C1       | D001 | bigString|bigString|bigString
C1       | D003 | bigString|bigString|bigString
C2       | D003 | bigString|bigString|bigString
...

Then on a second table, I have a list of boxes (in fact they are big plates in which goes the plates of 1st table, I call it box not to make confusion), in each box I have a list of elements inside, and define it like this.

ID     | NAME | NAME_ELEMENT | CONTRACT_ELEMENT| QUANTITY | 
10001  | BOX1 |         D001 |               C1|     100  |
10001  | BOX1 |         D003 |               C1|     100  |
10001  | BOX1 |         D003 |               C2|     100  |
...

I don't link on ID, but on CONTRACT and NAME, it is volontary(the first table is only the table with geometric definitions).

The problem is I have a ListView with list of boxes, and when I click on one box, I need to display all of the elements inside the box. For now I just do something like that :

    foreach(Plate plate in contexte.SelectedBox)
    {
       plate.GetGeometry();
    }

Edit : Forgot to say, my function GetGeometry is just a SQL request "SELECT * FROM DEFINITION_TABLE WHERE CONTRACT='C1' AND NAME='D001'.

It works good, but when I have a big plate, with hunders elements inside, it is very long, as it make hundreds of SELECT requests.

I see 2 ways to solve the problem :

  1. In my box definition table, add the geometric informations of details. But is this correct, as it means copy all definitions in both tables.

  2. Make a SQL request kind of

SELECT * 
FROM TABLE_DEFINITION 
WHERE (CONTRACT='C1' OR CONTRACT='C2') AND (NAME='D001' OR NAME='D003'...)
  1. The request may be
SELECT * 
FROM TABLE_DEFINITION 
WHERE (CONTRACT='C1' AND NAME='D001') OR (CONTRACT='C1' AND NAME='D003') OR (CONTRACT='C2' AND NAME='D003')...

But in the case I have hundreds details, the sentence would be very long.

I feel like 1st option is not the good one, and may use the 2nd one. What would you advise about it? Is it ok to make such a long sentence for MySQL? Am I limited on sentence length?

Edit : Thanks @Akina for editing post, could you please explain how you did to add spaces in table? I added spaces, but stack automatically deleted them all?

Solution

  • Use the form

    WHERE (CONTRACT, NAME) IN (('C1','D001'), ('C1','D003'), ('C2','D003'), ...)

    it takes less bytes.

    is it ok to make such a long query for MySQL?

    Look for max_allowed_packet value - your query text size must not exceed it (by default it is 64 Mbytes).

    Remember - if your literals list is over ~10 elements MySQL will sort it before using it in a query.