In terms of the software performance, there's no difference which one to use. The fact that you have switched to InnoDB has nothing to do with the crashing and locking users requests. 

The disadvantage of InnoDB, that you will have to backup more often, also InnoDB has its own separate storage place (file), in case it is lost, you won't be able to restore it. In this regard MyISAM has more flexible restoration workflow. 

you can google more detailed info. 

Hi Ross
in this case the problem is that I'm not able to do queries with usual way as it happens with foreign keys
so which is the better way for example to do a query where profile question  is "music" inside profile question section "Interests" ?  and how can I do a select for a specific user? (with interests, date_registration and so on?

Anyway is it possible to pass to a version with foreign keys?
'cause we ahve all informations inside the same table : ow_base_question_data and it seems difficult to retrieve informations for specific questions or specific users.

Thanks

Moonwald

you might want to read this before changing to InnoDB

http://www.oxwall.org/forum/topic/35946?page=1#post-145108

Hi, I changed DB engine to InnoDB, and I note a significant improvement in the speed of queries, because InnoDb support "row level locking" - support simultaneous write access by multiple sessions, making them suitable for multi-user, highly concurrent.

MyISAM uses "tablelevel locking" - allowing only one session to update those tables at a time. /it makes "waiting for table lock" -and that slows the query.

moonwald,  does the dave's answer help you to find an answer to your question ?
Peter, do you have more questions about database and connected to this topic?

Thanks Taissa

But in this case is it necessary to change ow_base_dao.sql with foreign keys?

In the case of passage to InnoDB is it possible to add inner join for searching users interests?

I mean: is it possible to add join (ow_base_user, ow_base_avatar, ow_base_question) inside ow_base_dao.sql OR is it not necessary?

Is it possible to execure query with actual ISAM for the same purpose
to do select in order to check all base_question divided by sub_questions (example: food -> chinese food?)

Thanks

Moonwald

moonwald, could you please clarify why you need to use the "foreign keys".
Where did you get the ow_base_dao.sql file?

So, if you need to get the these values: match_sex(Looking for), realname, birthdate for all users you may try running this query:

SELECT u.username, d.`intValue` as 'match_sex', d1.`textValue` as 'realname', d2.`dateValue` as 'birthdate'  FROM ow_base_user u 
INNER JOIN ow_base_question_data d ON ( u.id = d.userId and d.questionName = 'match_sex' )
INNER JOIN ow_base_question_data d1 ON ( u.id = d1.userId and d1.questionName = 'realname' )
INNER JOIN ow_base_question_data d2 ON ( u.id = d2.userId and d2.questionName = 'birthdate' )

Thank you very much Taissa

maybe I need more time in order to understand the relationship among ow_base_question_data, ow_base_question_value and ow_base_question

Infact I do not understand 'cause we have the columns "questionName" and  ow_base_question."name" not in clear mode: we have great varchars /values full of numbers and characters so it's difficult to retrieve the values that we see on the pages..

Moonwald

Moonwald,  I am really sorry but I didn't get the idea of your request. Can you either rephrase it or give me more details and examples for better understanding?

Which is the relationship between ow_base_question_data and ow_base_question_value?

For example I have into ow_base_question_value VALUES (questionName) without a precise meaning

INSERT INTO `ow_base_question_value` VALUES (309,'4971fc7002dca728f9a7f2a417c5284e',512,4);INSERT INTO `ow_base_question_value` VALUES (310,'match_sex',1,0);INSERT INTO `ow_base_question_value` VALUES (311,'match_sex',2,1);
I have the same into ow_base_question_dataow_base_question_datado you know why?

Moonwald, here is the list of tables: 
ow_base_question  - here the list of questions.
ow_base_question_data - here the questions and data users selected are saved.
ow_base_question_value - here is the data for questions of "multiple choice" type is saved ( for example list, multi list, radio button). Values of questions themselves ( for example  d68489df439fe45427e305a0e2dbe349_1 -  has a value "USA") are saved under languages.

But Taissa,
why the values of questions themselves (ow_base_question_value) are in not so clear/understandable meaning? Why was this value inserted with this code "d68489df439fe45427e305a0e2dbe349_1" at the place of "USA" directly ?

And why these values are saved in the same column? How is it possible to manage better the values of different multi list? In different columns
If the question food has the multi list : chinese, japanese, indian why does it put in a same column all these values? and in code not clear as you mentioned with the example:

d68489df439fe45427e305a0e2dbe349_1 -   value "USA"?

So Taissa,

taking your past query:SELECT u.username, d.`intValue` as 'match_sex', d1.`textValue` as 'realname', d2.`dateValue` as 'birthdate'  FROM ow_base_user u  
INNER JOIN ow_base_question_data d ON ( u.id = d.userId and d.questionName = 'match_sex' ) 
INNER JOIN ow_base_question_data d1 ON ( u.id = d1.userId and d1.questionName = 'realname' ) 
INNER JOIN ow_base_question_data d2 ON ( u.id = d2.userId and d2.questionName = 'birthdate' ) 
what does it need to add in this query in order to know value/values the key/keys associated with a specific multiple choice and the relative section profile question of all users?

Moonwald, you can't do it by using only SQL queries you will need to use php code like this one, for example:
for( $i = 0 ; $i < 31; $i++ )
{
    $val = pow( 2, $i );
    if ( (int)$sex & $val  )
    {
        $sexValue .= BOL_QuestionService::getInstance()->getQuestionValueLang('sex', $val) . ', ';
    }
}
So if I understand you correctly your task requires a plugin creating  and  involves the use  of different functions and methods, for example these ones:
-base.questions_field_init
-base.questions_save_data
-base.questions_get_data
base.question.search_sql.

And also use the methods of the language_service.php file.

Taissa,
so is it not possible to do a query in order to know how many and who is the user that filled the key/value of aspecific multiple choice values of a relative section profile question?

:-(

Thanks

Moonwald

Moonwald, here is the simple query example how you may get the users ID list: SELECT userId FROM `ow_base_question_data` WHERE `questionName` = 'question' AND `intValue` & {intValue}

where `questionName` is the required profile question hash value, for example field_2e5aeef45baae9a386bda11d8a653d76
and {intValue} - the multiple choice value from ow_base_question_value table or you may see it in language section.

so, for example you should get such a request:

SELECT `userId` FROM `ow_base_question_data` WHERE `questionName` = 'field_2e5aeef45baae9a386bda11d8a653d76' AND `intValue` & 2

And then you will need to use the following services to work with user list:
BOL_UserService
BOL_QuestionService

One curiosity, Taissa

why the necessity to insert all the informations about users interests into a unique table as
ow_base_question_data?

this table cannot be so great!

For example 'questionName' is repeated multiple times
maybe was it better to create a relational table where to insert the link to the field question Name?

Thanks

Moonwald

so for the migration to other database
in order to have the same questions with multiple choice in the section QUESTIONS INTERESTS area
is it only necessary to make the dump of these tables?

ow_base_language_key
ow_base_question_data
ow_base_question_value
ow_base_question

Kind Regards

Moonwald