question about mysql table and performance
- ericmuc
- OFFLINE
-
Platinum Member
- Posts: 411
- Thanks: 30
- Karma: 2
Add-ons
Add-ons
3 years 3 weeks ago - 3 years 3 weeks ago #323990
by ericmuc
question about mysql table and performance was created by ericmuc
Hi,
For implementation a bigger mysql table for using with query drop down field I have two possibilites:
1) scheme1
code, column_de1, column_de2, column_de3, column_xx1, column_xx2, column_xx3, ...
about 3000 rows per column, gives with 7 languages = 3000 rows
in comprofiler table 22 new columns
2) scheme 2
code, lang, column1, column2, column3
for every language 3000 rows, gives with 7 languages = 21000 rows
in comprofiler only 5 new columns
What is the best decision for the best website performance? Do I have to keep the columns number in comprofiler table as low as possible?
Thanks, best regards
Eric
For implementation a bigger mysql table for using with query drop down field I have two possibilites:
1) scheme1
code, column_de1, column_de2, column_de3, column_xx1, column_xx2, column_xx3, ...
about 3000 rows per column, gives with 7 languages = 3000 rows
in comprofiler table 22 new columns
2) scheme 2
code, lang, column1, column2, column3
for every language 3000 rows, gives with 7 languages = 21000 rows
in comprofiler only 5 new columns
What is the best decision for the best website performance? Do I have to keep the columns number in comprofiler table as low as possible?
Thanks, best regards
Eric
Last edit: 3 years 3 weeks ago by ericmuc.
Please Log in to join the conversation.
krileon
Team Member- ONLINE
- Posts: 68454
- Thanks: 9068
- Karma: 1434
3 years 3 weeks ago #323994
by krileon
Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.
Replied by krileon on topic question about mysql table and performance
Number of rows is never an issue as long as the queries you're using are properly indexed. There are a maximum number of columns that you can add to a table though before you hit row size limits. So my suggestion is schema 2. Why are you storing language specific information in the database though? You should be storing a generic language agnostic key that you translate on display.
Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.
Please Log in to join the conversation.
- ericmuc
- OFFLINE
-
Platinum Member
- Posts: 411
- Thanks: 30
- Karma: 2
Add-ons
Add-ons
3 years 3 weeks ago #323996
by ericmuc
Replied by ericmuc on topic question about mysql table and performance
Hi,
Thanks for the recommendation.
I have learned, that only the system with single-select or multi-select drop down with the query including language substitutions if-clauses and storing language data in mysql works also in multilanguage userlist search.
If I store the language codes and make translations with the language overrides, the search don't work with the language names, right?
That is my understanding, please check and let me know, if I am too complicate.
Thank you very much.
Best regards
Eric
Thanks for the recommendation.
I have learned, that only the system with single-select or multi-select drop down with the query including language substitutions if-clauses and storing language data in mysql works also in multilanguage userlist search.
If I store the language codes and make translations with the language overrides, the search don't work with the language names, right?
That is my understanding, please check and let me know, if I am too complicate.
Thank you very much.
Best regards
Eric
Please Log in to join the conversation.
- krileon
- Team Member
- ONLINE
- Posts: 68454
- Thanks: 9068
- Karma: 1434
3 years 3 weeks ago #324003
by krileon
Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.
Replied by krileon on topic question about mysql table and performance
If you're using a select fieldtype then the stored value should be language agnostic. The displayed value will translate fine. Example as follows.
Value: value_1
Label: Hello
The above results in the dropdown showing Hello while storing in the database value_1. The label "Hello" can be translated using CB language overrides. You do not need to store any kind of language specific value for this to work. During searching users would use that same dropdown where translation API would again apply.
Value: value_1
Label: Hello
The above results in the dropdown showing Hello while storing in the database value_1. The label "Hello" can be translated using CB language overrides. You do not need to store any kind of language specific value for this to work. During searching users would use that same dropdown where translation API would again apply.
Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.
The following user(s) said Thank You: ericmuc
Please Log in to join the conversation.
Moderators: beat, nant, krileon
Time to create page: 0.197 seconds
-
You are here:
- Home
- Forums
- Support and Presales
- Developer Members Support
- question about mysql table and performance