marrowyung
asked on
MariaDB coding standard
Hi,
any anti pattern of MariaDB coding standard ? good programming practice for MariaDB from application level point of view ?
please share here, tks.
any anti pattern of MariaDB coding standard ? good programming practice for MariaDB from application level point of view ?
please share here, tks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You probably want to use prepared queries in order to avoid code injections. In a web server context, using var_export in php is also acceptable afaik. The real_escape_string function that comes with mysql clients is more bullet proof but wil break binary data
Regarding naming conventions, i d advise the above anx additionally name ids "id" and foreign keys "id_REMOTETBLNAME" to provide easy to understand db schemas and queries
ASKER
Chris Stanyon,
"or they're set by the head of the development team. "
Excellent and we believe so, but as DB team here also has to do sth as nowadays programmers here can miss a lot of thing and jet up the DB server. and OSDB always can't handle high loading as efficient as paid DB like MS SQL, Oracle and DB2.
" not just for MariaDb but for your whole devevlopment stack (php, css, javascript etc)."
at this moment just DB as we are not development team !
"You'll find suggestions for pretty much all the programming languages on the net. For example, Microsoft has one for C# and PHP has the PSR-2 standard
"
I want one only for MariaDB, not for other DB, any resource can share with me from developer's point of view ?
" once you've decided on the standards, stick to them."
this is what I am checking out, please share any real fact/standard/practice ! .e.g like no sub queries but inner join. NO select distinct but group by.
": it will be more about naming conventions than coding standards. "
I am suggested to open this new ticket on coding standard and we have name conventions already.
skullnobrains,
" i d advise the above anx additionally name ids "id""
What is ids ? "is", right ?
what is anx ?
""id_REMOTETBLNAME" to provide easy to understand db schemas and queries"
this means this is a foreign key id point to the same field in <remote table> ?
"or they're set by the head of the development team. "
Excellent and we believe so, but as DB team here also has to do sth as nowadays programmers here can miss a lot of thing and jet up the DB server. and OSDB always can't handle high loading as efficient as paid DB like MS SQL, Oracle and DB2.
" not just for MariaDb but for your whole devevlopment stack (php, css, javascript etc)."
at this moment just DB as we are not development team !
"You'll find suggestions for pretty much all the programming languages on the net. For example, Microsoft has one for C# and PHP has the PSR-2 standard
"
I want one only for MariaDB, not for other DB, any resource can share with me from developer's point of view ?
" once you've decided on the standards, stick to them."
this is what I am checking out, please share any real fact/standard/practice ! .e.g like no sub queries but inner join. NO select distinct but group by.
": it will be more about naming conventions than coding standards. "
I am suggested to open this new ticket on coding standard and we have name conventions already.
skullnobrains,
" i d advise the above anx additionally name ids "id""
What is ids ? "is", right ?
what is anx ?
""id_REMOTETBLNAME" to provide easy to understand db schemas and queries"
this means this is a foreign key id point to the same field in <remote table> ?
ASKER
I am looking at this :
https://www.tutorialspoint.com/mariadb/mariadb_useful_functions.htm
is it still vaild for MariaDB 10.3.15 GA ?
like:
1) https://www.tutorialspoint.com/mariadb/mariadb_sql_injection_protection.htm
2) how about join:
https://www.tutorialspoint.com/mariadb/mariadb_join.htm
anything to care about inner join in MariaDB?
3) how to handle duplicate item:
https://www.tutorialspoint.com/mariadb/mariadb_managing_duplicates.htm
I will say more like when creating MariaDB table, we must define primary key, foreigh key and unique constraint, is that also right for MariaDB?
https://www.tutorialspoint.com/mariadb/mariadb_useful_functions.htm
is it still vaild for MariaDB 10.3.15 GA ?
like:
1) https://www.tutorialspoint.com/mariadb/mariadb_sql_injection_protection.htm
2) how about join:
https://www.tutorialspoint.com/mariadb/mariadb_join.htm
anything to care about inner join in MariaDB?
3) how to handle duplicate item:
https://www.tutorialspoint.com/mariadb/mariadb_managing_duplicates.htm
I will say more like when creating MariaDB table, we must define primary key, foreigh key and unique constraint, is that also right for MariaDB?
Hi there,
As far as I'm aware, there are no coding standards for MariaDb. There are guidelines, some of which I've outlined above, but these are simply naming conventions that you choose to adopt. It helps you keep consistency in your DBs, making it easier to work on them. If you don't have a head of developement, then 'someone' will have to decide on these conventions that you or your team adhere to.
The comment about ids means that when you have a primary auto-increment key, make sure you call it id on all of your tables. Anx is just a spelling mistake and should have said 'and'.
id_REMOTETBLNAME means that when you have a foreign key on your table, you name it by using the name of the key from the other table, along with the name of the other table. For example, if you have a Customers table and an Orders table, Your Orders table would contain a foreign key called id_order (or order_id).
All of those functions that you link to are valid for MariaDB.
Joins are a standard part of any DB. How efficient they are will depend in part on the indices you've created and complexity of the join. Normalising your Database will also play a part in all of this, so plan carefully.
Preventing SQL injection is often done at the application level, so it would be up to your developers to code that using whatever language they choose. In the link you provided, the example shown is written in PHP.
Adding indices to your table is vital for performance and data integrity, so Primary Keys, Foreign Keys, Unique Constraints etc are a vital part of your DB design. They're as valid to MariaDB as they are to any other DB.
As far as I'm aware, there are no coding standards for MariaDb. There are guidelines, some of which I've outlined above, but these are simply naming conventions that you choose to adopt. It helps you keep consistency in your DBs, making it easier to work on them. If you don't have a head of developement, then 'someone' will have to decide on these conventions that you or your team adhere to.
The comment about ids means that when you have a primary auto-increment key, make sure you call it id on all of your tables. Anx is just a spelling mistake and should have said 'and'.
id_REMOTETBLNAME means that when you have a foreign key on your table, you name it by using the name of the key from the other table, along with the name of the other table. For example, if you have a Customers table and an Orders table, Your Orders table would contain a foreign key called id_order (or order_id).
All of those functions that you link to are valid for MariaDB.
Joins are a standard part of any DB. How efficient they are will depend in part on the indices you've created and complexity of the join. Normalising your Database will also play a part in all of this, so plan carefully.
Preventing SQL injection is often done at the application level, so it would be up to your developers to code that using whatever language they choose. In the link you provided, the example shown is written in PHP.
Adding indices to your table is vital for performance and data integrity, so Primary Keys, Foreign Keys, Unique Constraints etc are a vital part of your DB design. They're as valid to MariaDB as they are to any other DB.
ASKER
"but these are simply naming conventions that you choose to adopt. It helps you keep consistency in your DBs, making it easier to work on them"
tks.
so we still focus on naming conversion .
"The comment about ids means that when you have a primary auto-increment key, make sure you call it id on all of your tables.
yeah, from time to time auto increment filed use 'id' as the name
"Joins are a standard part of any DB. How efficient they are will depend in part on the indices you've created and complexity of the join. Normalising your Database will also play a part in all of this, so plan carefully."
yeah, but in MS SQL, there are way to fix the inner join problem, how about MariaDB ?
"Preventing SQL injection is often done at the application level, so it would be up to your developers to code that using whatever language they choose. In the link you provided, the example shown is written in PHP."
tks.
their job, not my job then..
"Adding indices to your table is vital for performance and data integrity, so Primary Keys, Foreign Keys, Unique Constraints etc are a vital part of your DB design. They're as valid to MariaDB as they are to any other DB.
Is this your solution?"
tks.
tks.
so we still focus on naming conversion .
"The comment about ids means that when you have a primary auto-increment key, make sure you call it id on all of your tables.
yeah, from time to time auto increment filed use 'id' as the name
"Joins are a standard part of any DB. How efficient they are will depend in part on the indices you've created and complexity of the join. Normalising your Database will also play a part in all of this, so plan carefully."
yeah, but in MS SQL, there are way to fix the inner join problem, how about MariaDB ?
"Preventing SQL injection is often done at the application level, so it would be up to your developers to code that using whatever language they choose. In the link you provided, the example shown is written in PHP."
tks.
their job, not my job then..
"Adding indices to your table is vital for performance and data integrity, so Primary Keys, Foreign Keys, Unique Constraints etc are a vital part of your DB design. They're as valid to MariaDB as they are to any other DB.
Is this your solution?"
tks.
Not sure what you mean by 'fix the inner join problem'
I guess he means using subqueries ratjer than inner joins.
Mssql used to be better at handling subqueries than joins for many operations such as consistency checks. And mysql was the exact oposite until 5 dot something. Nowadays the mysql optimiser udually handles "in select ..." statements and joins equivalently.
I guess the best practice whatever the db is to run EXPLAIN or equivalent statements against a fully loaded database and see how the query is processed.
It is also good practice to ude the slow query log. And look at innodb deadlocks once in a while.
I used to stick expected and max query times in sql comments and have a tiny daemon reporting oddnesses and killing queries in some cases based on the output of "show full processlist ". This prooved quite useful to track drastic cha'ges in execution times. Nowadays there are audit plugins that could help focus on the right queries.
But none of the above replaces properly thinking the algorithms and indexes.
As an example, when building statistics, it is often more efficient both in terms of performance and disk space to build stats tables with triggers than index the original table.
There is no way to achieve spmething good performance wise when the developpers and db admins work separately.
Mssql used to be better at handling subqueries than joins for many operations such as consistency checks. And mysql was the exact oposite until 5 dot something. Nowadays the mysql optimiser udually handles "in select ..." statements and joins equivalently.
I guess the best practice whatever the db is to run EXPLAIN or equivalent statements against a fully loaded database and see how the query is processed.
It is also good practice to ude the slow query log. And look at innodb deadlocks once in a while.
I used to stick expected and max query times in sql comments and have a tiny daemon reporting oddnesses and killing queries in some cases based on the output of "show full processlist ". This prooved quite useful to track drastic cha'ges in execution times. Nowadays there are audit plugins that could help focus on the right queries.
But none of the above replaces properly thinking the algorithms and indexes.
As an example, when building statistics, it is often more efficient both in terms of performance and disk space to build stats tables with triggers than index the original table.
There is no way to achieve spmething good performance wise when the developpers and db admins work separately.
@marrowyung : you may wish to stop bashing free software by stating free is slower. There are many areas in which mysql will vastly outperform oracle and that mostly depends on exposed features. We have MUCH more experience at such comparisons. Probably enough to write a truckload of books including elements such as clusterisation methods, transaction isolation levels, base hardware, ... and i do not believe anyone with such experience could clearly state one db is faster than another.
ASKER
skullnobrains,
"I guess he means using subqueries ratjer than inner joins."
nonon. inner join is a preferred way to use than subqueries, but if we have too many inner join, there will be other problems.
this is a common case in MS SQL, and we have a way to fix this kind of shit !
imagine that there are 50 inner joins (Real case !), it will be slow and how we fix it?
"And mysql was the exact oposite until 5 dot something. Nowadays the mysql optimiser udually handles "in select ..." statements and joins equivalently."
tks. and it seems DBA and developer do not have to worry about it any more ! AhAHHA ! much less work to do !
"It is also good practice to ude the slow query log. And look at innodb deadlocks once in a while."
I prefer to see the real time query instead of slow query log. like in MS SQL server. can slow query log see the problem in real time ?
"Nowadays there are audit plugins that could help focus on the right queries."
in MySQL DB level ? this kind of thing need to do this at audit level ?
"I used to stick expected and max query times in sql comments and have a tiny daemon reporting oddnesses and killing queries in some cases based on the output of "show full processlist ". This prooved quite useful to track drastic cha'ges in execution times"
this one is not enough to check why that slow, right ?
"There is no way to achieve spmething good performance wise when the developpers and db admins work separately."
so what should be the good approach for it ?
"As an example, when building statistics, it is often more efficient both in terms of performance and disk space to build stats tables with triggers than index the original table.
"
build separate stable to keep statistics only ? or what do you mean ,I don't understand.
"and i do not believe anyone with such experience could clearly state one db is faster than another."
you mean no DB can be absolutely BETTER or FASTER than other DB, no matter it is open source or proprietary DB ?
"I guess he means using subqueries ratjer than inner joins."
nonon. inner join is a preferred way to use than subqueries, but if we have too many inner join, there will be other problems.
this is a common case in MS SQL, and we have a way to fix this kind of shit !
imagine that there are 50 inner joins (Real case !), it will be slow and how we fix it?
"And mysql was the exact oposite until 5 dot something. Nowadays the mysql optimiser udually handles "in select ..." statements and joins equivalently."
tks. and it seems DBA and developer do not have to worry about it any more ! AhAHHA ! much less work to do !
"It is also good practice to ude the slow query log. And look at innodb deadlocks once in a while."
I prefer to see the real time query instead of slow query log. like in MS SQL server. can slow query log see the problem in real time ?
"Nowadays there are audit plugins that could help focus on the right queries."
in MySQL DB level ? this kind of thing need to do this at audit level ?
"I used to stick expected and max query times in sql comments and have a tiny daemon reporting oddnesses and killing queries in some cases based on the output of "show full processlist ". This prooved quite useful to track drastic cha'ges in execution times"
this one is not enough to check why that slow, right ?
"There is no way to achieve spmething good performance wise when the developpers and db admins work separately."
so what should be the good approach for it ?
"As an example, when building statistics, it is often more efficient both in terms of performance and disk space to build stats tables with triggers than index the original table.
"
build separate stable to keep statistics only ? or what do you mean ,I don't understand.
"and i do not believe anyone with such experience could clearly state one db is faster than another."
you mean no DB can be absolutely BETTER or FASTER than other DB, no matter it is open source or proprietary DB ?
ASKER
back to the question, so no other coding guide for MySQL/MariaDB?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
"there is no way to answer that directly without seeing the execution plan. in mysql, you need to run "EXPLAIN SELECT ..." to get the exec plan. you need both the DBAs and developpers to be able to understand the output."
this is one of the problem inner join can cause, I can tell you, the fix usual gives the SAME execution plan in MS SQL, just like select distinct and group by, but performance big diff.
usually real time figure can show it is a big diff but execution plan will tells you it is the SAME.
"as a side note, running queries with 50 joins usually mean there is something wrong with the schema."
yes you can say it ! but what if no way to fix the design and some business requirement gives this ?
"i do not understand"
I mean why we need audit to check that out ?
"another example on a different project would be using daily tablespaces to prevent deletions from killing the whole database"
so you mean for any TEMPORARY we have, create in separate tablespace and when we don't need it , just drop that table space?
this is not for permanent data, right?
", while the actual better approach was to build separate tables automagically."
so this is build stats tables with triggers than index the original table, then when we are selecting from original table, how can we ask MySQL/MariaDB to check this stats table .
we are creating more and more table because of this ! so we double up the number of table ?
"mysql myisam will allow crazy insertion rythms, and actually match the disk write speed when using deferred insertions but that would not be acid compliant."
yeah, myisam do not have ACID, but it also means if we don't need this, it can be very fast!
" if one db was actually much better than the others, the others would simply cease to exist. each db will be better that the others in specific fields."
sure ! just like NoSQL, it is commonly use a on RAM file system for apps download and it do'nt have much ACID and DBAs understand this.
it is on RAM but if battery power lost, all data lost.
this is one of the problem inner join can cause, I can tell you, the fix usual gives the SAME execution plan in MS SQL, just like select distinct and group by, but performance big diff.
usually real time figure can show it is a big diff but execution plan will tells you it is the SAME.
"as a side note, running queries with 50 joins usually mean there is something wrong with the schema."
yes you can say it ! but what if no way to fix the design and some business requirement gives this ?
"i do not understand"
I mean why we need audit to check that out ?
"another example on a different project would be using daily tablespaces to prevent deletions from killing the whole database"
so you mean for any TEMPORARY we have, create in separate tablespace and when we don't need it , just drop that table space?
this is not for permanent data, right?
", while the actual better approach was to build separate tables automagically."
so this is build stats tables with triggers than index the original table, then when we are selecting from original table, how can we ask MySQL/MariaDB to check this stats table .
we are creating more and more table because of this ! so we double up the number of table ?
"mysql myisam will allow crazy insertion rythms, and actually match the disk write speed when using deferred insertions but that would not be acid compliant."
yeah, myisam do not have ACID, but it also means if we don't need this, it can be very fast!
" if one db was actually much better than the others, the others would simply cease to exist. each db will be better that the others in specific fields."
sure ! just like NoSQL, it is commonly use a on RAM file system for apps download and it do'nt have much ACID and DBAs understand this.
it is on RAM but if battery power lost, all data lost.
ASKER
also PostgreSQL parallel execution is good !
actually, most dbs do parallel execution to some extent, and the postgres implementation is afaik only likely to bring a mesurable gain for queries that do not do many joins and are very cpu intensive... pretty much none in real life scenaris.
mariadb/innodb will use multiple workers when filling/flushing the buffer cache, but a single worker for the actual query. this is good enough in most cases, because queries are mostly I/O bound.
oracle works similarly
mssql relies on the operating system and ntfs driver for parallel I/O
infindb fully supports parallel execution afaik but i haven't used it in years
what makes you tell it is "good" ? did you run any benchmark or solve a real life problem ? if you did, please share.
mariadb/innodb will use multiple workers when filling/flushing the buffer cache, but a single worker for the actual query. this is good enough in most cases, because queries are mostly I/O bound.
oracle works similarly
mssql relies on the operating system and ntfs driver for parallel I/O
infindb fully supports parallel execution afaik but i haven't used it in years
what makes you tell it is "good" ? did you run any benchmark or solve a real life problem ? if you did, please share.
ASKER
hi.
"mariadb/innodb will use multiple workers when filling/flushing the buffer cache, but a single worker for the actual query. this is good enough in most cases, because queries are mostly I/O bound."
all queries is mostly I/O bounded as they load data from disk.
I think only DB like NoSQL is not I/O bound.
what we discussed will change from version to version.
we have to stop here as this post is about coding standard and I have to consolidate what we discussed here.
BTW,
"You probably want to use prepared queries in order to avoid code injections. "
what is prepared queries, please give an example.
"build both the data model and the algorithms that should be used. there are plenty of examples of projects that could hardly run on server grade expensive hardware that i got to run and expand tenfolds using commodity hardware just by being able to oversee both sides.
"
do you mean this one is the DB architecture? he/she has to do data modelling ?
"mariadb/innodb will use multiple workers when filling/flushing the buffer cache, but a single worker for the actual query. this is good enough in most cases, because queries are mostly I/O bound."
all queries is mostly I/O bounded as they load data from disk.
I think only DB like NoSQL is not I/O bound.
what we discussed will change from version to version.
we have to stop here as this post is about coding standard and I have to consolidate what we discussed here.
BTW,
"You probably want to use prepared queries in order to avoid code injections. "
what is prepared queries, please give an example.
"build both the data model and the algorithms that should be used. there are plenty of examples of projects that could hardly run on server grade expensive hardware that i got to run and expand tenfolds using commodity hardware just by being able to oversee both sides.
"
do you mean this one is the DB architecture? he/she has to do data modelling ?
what is prepared queries, please give an example.
if you have not googled that yet, you should do that right now. this is more than properly covered in documentations.
basically write queries with tokens and map the tokens to variables.
example query : update tableX set val=? where id=?
and you would simply "call" the query with 2 parameters that will replace each of the "?"
the "?" tokens are automatically handled by the API which will properly handle quoting and escaping where needed. additionally, the same query is parsed only once when you prepare it and can be reused multiple times.
do you mean this one is the DB architecture? he/she has to do data modelling ?
if possible that person would do the data modelling, chosse the required backends, define some of the algorythms, and most likely also handle at least part of the network shema, data distribution, involved software... basically that's part of what an "architect" does... or should do.
ASKER
"the "?" tokens are automatically handled by the API which will properly handle quoting and escaping where needed. additionally, the same query is parsed only once when you prepare it and can be reused multiple times."
ok, like MS SQL view, pre compiled in order to use the same execution plan but it will not !
result set diff from time to time.
"You probably want to use prepared queries in order to avoid code injections."
then I am not sure why prepared queries can be for this purpose? logic is predefined and system is not going to accept queries not predefined ?
"network shema, d"
DB architecture need to do network too ?
I have this question too, please come and help if you have time:
https://www.experts-exchange.com/questions/29149807/upgrade-MariaDB-to-10-4-6.html?anchor=a42892698¬ificationFollowed=232256864&anchorAnswerId=42892698#a42892698
ok, like MS SQL view, pre compiled in order to use the same execution plan but it will not !
result set diff from time to time.
"You probably want to use prepared queries in order to avoid code injections."
then I am not sure why prepared queries can be for this purpose? logic is predefined and system is not going to accept queries not predefined ?
"network shema, d"
DB architecture need to do network too ?
I have this question too, please come and help if you have time:
https://www.experts-exchange.com/questions/29149807/upgrade-MariaDB-to-10-4-6.html?anchor=a42892698¬ificationFollowed=232256864&anchorAnswerId=42892698#a42892698
I will have time if you bother using complete sentences. I hardly understand the first ( and never mentionned anything regarding execution plan ) and do not understand the second at all. Sorry but i have no time for that.
ASKER
tks both.