Design of web application: search in multiple tables by params

General Architecture, design, OOP
1

I have a question about organizing my web application. I have many tables. I must search in it by different parameters. for example: country: 2 and category1: 33 and language: 3.


select distinct s.id from tbl_item s

left join tbl_item_act ep on s.id = ep.item_id

left join tbl_item_act_category cl3 on cl3.id = ep.category_id

left join tbl_item_act_category cl2 on cl2.id = cl3.parent_id

left join tbl_item_act_category cl1 on cl1.id = cl2.parent_id

left join tbl_city ci on ci.id = s.city_id

left join tbl_country co on co.id = ci.country_id

left join tbl_language la on la.id = cl2.language_id

left join tbl_item_subject sbb on sbb.item_id = s.id

left join tbl_subject sb on sb.id = sbb.subject_id and sb.subject_type = 'subject'

left join tbl_subject ev on ev.id = sbb.subject_id and ev.subject_type = 'event'

left join tbl_season se on se.id = cl1.season_id or se.id = cl2.season_id

left join tbl_some_type et on et.id = s.education_type_id

left join tbl_item_tag_binding stagb on stagb.item_id = s.id 

left join tbl_item_tag stag on stag.id = stagb.tag_id

left join tbl_item_type_binding stb on stb.item_id = s.id

left join tbl_item_type st on st.id = stb.item_type_id

where s.original_id is null and cl1.id = 33 and la.id = 2 and se.id = 2

I must execute 9-10 queries on one page. I takes about 50ms each query. I tried to generate one table (such as view) and store joined data here (as cache table) but it stores millions rows and searching takes about 500ms each query. I tried nosql (mongodb) search (big data tree) and it takes a little time - about 20-30 ms but it is much to me.

What the best practices to create fast search by many parameters in my data structure?

sorry for my english.

2

  1. Make sure you have all indexes necessary so there’s no file scan. You can do so by executing “EXPLAIN YOUR_SQL” and checking the output.

  2. Try to denormalize data so everything’s in a single table.

3

Samdark, thank you for your reply!

  1. All indexes exist.

  2. I compiled all structure in a single table which have 1 200 000 rows and index it. Simple query with one condition takes 500-800ms.

select distinct sid from tbl_catalog_view where cl1id = 33

…

345 rows in set (0.78 sec)

[size="2"]


MariaDB [portal]> explain select distinct sid from tbl_catalog_view where cl1id = 33;

+------+-------------+------------------+------+---------------+------+---------+-------+--------+------------------------------+

| id   | select_type | table            | type | possible_keys | key  | key_len | ref   | rows   | Extra                        |

+------+-------------+------------------+------+---------------+------+---------+-------+--------+-----------------

|    1 | SIMPLE      | tbl_catalog_view | ref  | cv_1          | cv_1 | 5       | const | 571758 | Using where; Using temporary |

+------+-------------+------------------+------+---------------+------+---------+-------+--------+-----------------

[/size]

without distinct: 585017 rows in set (0.73 sec)

[size="2"]




MariaDB [portal]> explain select (sid) from tbl_catalog_view where cl1id = 33;

+------+-------------+------------------+------+---------------+------+---------+-------+--------+-------+

| id   | select_type | table            | type | possible_keys | key  | key_len | ref   | rows   | Extra |

+------+-------------+------------------+------+---------------+------+---------+-------+--------+-------+

|    1 | SIMPLE      | tbl_catalog_view | ref  | cv_1          | cv_1 | 5       | const | 571758 |       |

+------+-------------+------------------+------+---------------+------+---------+-------+--------+-------+

1 row in set (0.00 sec)

[/size]

4

What’s cv_1 key?

5 

create index cv_1 on tbl_catalog_view (cl1id)



CREATE TABLE `tbl_catalog_view` (

  `sid` int(11) DEFAULT NULL,

  `cl3id` int(11) DEFAULT NULL,

  `cl2id` int(11) DEFAULT NULL,

  `cl1id` int(11) DEFAULT NULL,

  `ciid` int(11) DEFAULT NULL,

  `coid` int(11) DEFAULT NULL,

  `laid` int(11) DEFAULT NULL,

  `sbid` int(11) DEFAULT NULL,

  `evid` int(11) DEFAULT NULL,

  `seid` int(11) DEFAULT NULL,

  `etid` int(11) DEFAULT NULL,

  `stagid` int(11) DEFAULT NULL,

  `stid` int(11) DEFAULT NULL,

  `age_from` int(11) DEFAULT NULL,

  `age_to` int(11) DEFAULT NULL,

  KEY `cv_2` (`sid`),

  KEY `cv_1` (`cl1id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

Could I make mistake while creating indexes?

6

Seems to be correct. Is it InnoDB? Do you have free memory to increase innodb_buffer_pool_size?

7

I increased it to 1GB but it is no effect


select distinct sid from tbl_catalog_view where cl1id = 33;

345 rows in set (2,29 sec)



mysql> show profile;

+----------------------+----------+

| Status               | Duration |

+----------------------+----------+

| starting             | 0.000089 |

| checking permissions | 0.000011 |

| Opening tables       | 0.000022 |

| init                 | 0.000030 |

| System lock          | 0.000014 |

| optimizing           | 0.000023 |

| statistics           | 0.000120 |

| preparing            | 0.000033 |

| Creating tmp table   | 0.000033 |

| executing            | 0.000004 |

| Sending data         | 2.240334 |

| end                  | 0.000013 |

| query end            | 0.000009 |

| removing tmp table   | 0.000006 |

| query end            | 0.000003 |

| closing tables       | 0.000006 |

| freeing items        | 0.000024 |

| cleaning up          | 0.000020 |

+----------------------+----------+

18 rows in set, 1 warning (0,00 sec)
8

Well, "sending data" is a result of "distinct", I think.

9

Problem is not resolved still. Is it right to make many left joins or compile all to one table (query from first message)? Or use nosql for data tree search?


        city--country

          \                                    language

           \                                     |

subject -- item --------------item_act----cl3---cl2---cl1

           /  \                                    \  /

          /    \                                  season

       event    type

I must select each entity in a tree depending of other entities.

For example:

  1. select item by cl2 and season

  2. select season by country and subject and language

  3. …