Complex Query strategy

Question

I'm developing a database and I need to create a query. The database will save the information from general alert having a main table "alert" that relate all the other tables information with that alert in the following way:

• The alert table contains the following columns: analyzer_id, create_time_id (...) and classification_id. ( 1 to n relation between the alert and the rest of the tables)

• The alert_has_source table contains the the following columns: alert_id and source_id. ( n to m relation between the alert and the source)

• The alert_has_targets contains the the following columns: alert_id and target_id. ( n to m relation between the alert and the target)

I'm amateur at mysql so i'm not sure if the database is well designed and this is the way to express the information.

I need to query some columns form the table analyzer, classification, create_time, source (with node and address), target(with node and address) that need to be filter by the the table classification.ident = "log".

1) Should I create a view in the database and provide it with the query? Does that perform well?

2) Regarding the query should I use multiple selects or the inner join?

3) How to optimize the search due to the fact that I only need some part of the database?

Answer 1

Your question is not as specific as it could be about the result set you require. That makes it difficult to answer specifically.

Nevertheless, here is an attempt.

1) Should I create a view in the database and provide it with the query?

You can do that. It's a convenient way to encapsulate a complex query. Get the query working first, then make a view from it.

Does that perform well?

In MySQL views typically have very similar performance to their single-query equivalents. Other databases (PostgreSQL, Oracle for example) have optimizations for views.

2) Regarding the query should I use multiple selects or the inner join?

If you need a certain result set -- a certain virtual table with one row per interesting item -- that's composed from your tables, you should use a strategy of JOINing the tables. If you need a bunch of loosely related data points from different tables, you can SELECT those data points separately.

INNER JOIN is a type of JOIN. Sometimes you may need LEFT JOINs instead. a LEFT JOIN b may serve you better when table a contains rows missing from table b. INNER JOIN suppresses rows from the result set unless they appear on both sides of the join.

3) How to optimize the search due to the fact that I only need some part of the database?

That's the purpose of indexes in SQL. You can read about it here. http://use-the-index-luke.com/