Yii Framework Forum: Design of web application: search in multiple tables by params - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Design of web application: search in multiple tables by params What the right way to build web app? technology stack

#1 User is offline   mitch3182 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 6
  • Joined: 04-October 12

Posted 20 October 2016 - 11:08 AM

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.
0

#2 User is offline   samdark 

  • Having fun
  • Yii
  • Group: Yii Dev Team
  • Posts: 5,184
  • Joined: 17-January 09
  • Location:Russia

Posted 20 October 2016 - 04:57 PM

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.
Yii 2.0 Development Cookbook

Enjoying Yii? Star us at github

Support me so I can work more on Yii: https://www.patreon.com/samdark
0

#3 User is offline   mitch3182 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 6
  • Joined: 04-October 12

Posted 21 October 2016 - 04:28 AM

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)


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 |
+------+-------------+------------------+------+---------------+------+---------+-------+--------+-----------------


without distinct: 585017 rows in set (0.73 sec)
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)


0

#4 User is offline   samdark 

  • Having fun
  • Yii
  • Group: Yii Dev Team
  • Posts: 5,184
  • Joined: 17-January 09
  • Location:Russia

Posted 21 October 2016 - 01:02 PM

What's cv_1 key?
Yii 2.0 Development Cookbook

Enjoying Yii? Star us at github

Support me so I can work more on Yii: https://www.patreon.com/samdark
0

#5 User is offline   mitch3182 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 6
  • Joined: 04-October 12

Posted 24 October 2016 - 02:22 AM

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?
0

#6 User is offline   samdark 

  • Having fun
  • Yii
  • Group: Yii Dev Team
  • Posts: 5,184
  • Joined: 17-January 09
  • Location:Russia

Posted 24 October 2016 - 04:12 AM

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

Enjoying Yii? Star us at github

Support me so I can work more on Yii: https://www.patreon.com/samdark
0

#7 User is offline   mitch3182 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 6
  • Joined: 04-October 12

Posted 24 October 2016 - 08:54 AM

View Postsamdark, on 24 October 2016 - 04:12 AM, said:

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


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)

0

#8 User is offline   samdark 

  • Having fun
  • Yii
  • Group: Yii Dev Team
  • Posts: 5,184
  • Joined: 17-January 09
  • Location:Russia

Posted 24 October 2016 - 12:12 PM

Well, "sending data" is a result of "distinct", I think.
Yii 2.0 Development Cookbook

Enjoying Yii? Star us at github

Support me so I can work more on Yii: https://www.patreon.com/samdark
0

#9 User is offline   mitch3182 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 6
  • Joined: 04-October 12

Posted 25 October 2016 - 04:32 AM

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. ...
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users