Utilisation de EXPLAIN de MySQL pour optimiser des requêtes SQL
▻https://www.mon-code.net/article/157/utilisation-de-la-fonction-explain-de-mysql-pour-optimiser-des-requetes-sq
un exemple d’utilisation de EXPLAIN et d’optimisation en jouant sur les index des tables
{par_ordre_liste...} - SPIP
▻https://www.spip.net/fr_article6646.html#par_ordre_liste
le critère de tri SPIP par_ordre_liste a un gros défaut : si on l’applique sur un ensemble d’éléments qui ne sont pas tous dans la #LISTE passée en 2ème paramètre, il affiche d’abord les éléments hors de la #LISTE puis ceux qui sont dedans.
Exemple si on a 16 sites syndiqués dans la BDD :
<BOUCLE_t(SYNDIC){par_ordre_liste id_syndic, #LISTE{14,16,13}}{id_syndic > 10}>11, 12, 15, 14, 16, 13SELECT * FROM spip_syndic AS syndic
WHERE syndic.id_rubrique > 10
ORDER BY FIELD (syndic.id_syndic, 14,16,13);Pour obtenir le résultat « naturellement attendu » (i.e. d’abord ceux de la liste puis les autres) il faut utiliser la requête SQL suivante :
SELECT * FROM spip_syndic AS syndic
WHERE syndic.id_rubrique > 10
ORDER BY IF (FIELD(syndic.id_syndic, 14,16,13)=0,1,0), FIELD(syndic.id_syndic,14,16,13);IF (FIELD ... en premier critère de la requète
bééé ticket donc, plutôt que seenthis non ? :)
oui effectivement (au départ c’était pour garder simplement trace du IF(FIELD(...)
▻https://git.spip.net/spip/spip/issues/5273 donc !
dolthub/dolt: Dolt – It’s Git for Data
▻https://github.com/dolthub/dolt
Dolt is a #SQL database that you can #fork, clone, branch, merge, push and pull just like a git repository. Connect to Dolt just like any MySQL database to run queries or update the data using SQL commands. Use the command line interface to import CSV files, commit your changes, push them to a remote, or merge your teammate’s changes.
All the commands you know for #Git work exactly the same for Dolt. Git versions files, Dolt versions tables. It’s like Git and MySQL had a baby.
Semble proposer à peu près tout le nécessaire pour remplacer MySQL/MariaDB :
– un serveur
– un client
– une interface de gestion à la phpmyadmin
...mais avec la puissance de Git pour gérer des modifications collaboratives (fok, merge, checkout...)
#dolt #mysql #mariadb #web_dev #git_pour_bdd
Après pour remplacer, faudra pas juste regarder les outils : faut voir comment ça tient la charge, en terme d’accès concurrents, de quantité de contenus, un site très visité, ou inversement beaucoup de modifs en même temps (beaucoup de rédacteurs/commentateurs, etc, toutes choses qui écrivent dans la base)
maxbube/mydumper: Official #mydumper project
▻https://github.com/maxbube/mydumper
What is mydumper? Why?
– Parallelism (hence, speed) and performance (avoids expensive character set conversion routines, efficient code overall)
– Easier to manage output (separate files for tables, dump metadata, etc, easy to view/parse data)
– Consistency - maintains snapshot across all threads, provides accurate master and slave log positions, etc
– Manageability - supports PCRE for specifying database and tables inclusions and exclusions
When it comes to backing up and restoring MySQL database, most people usually use the very popular mysqldump. Whilst, mysqldump can be very easy to use for a smaller database, it doesn’t work well with larger databases. It’s very slow for huge databases and very error prone when used for very big MySQL databases.
▻https://dotlayer.com/extremely-fast-mysql-backup-restore-using-mydumpermyloader
Semble être utilisé par wikmedia cf :
Its speed and parallelism makes it perfect to save time and load data a lot faster.
It is easier to recover single tables or rows.
It compress nicely
▻https://wikitech.wikimedia.org/wiki/MariaDB#Dumping_tables_with_mydumper
sort MySQL-ORDER BY valeurs dans IN() - Résolu
▻https://code.i-harness.com/fr/q/ea0a3#answer3
utiliser une liste pour ordonner les résultats d’une requete (typiquement faire unid_truc IN (1, 3, 5, 8)
et avoir les résultats triés selon le même ordre
ce qui se fait avec :SELECT * FROM spip_truc WHERE id_truc IN (1, 3, 5, 8) ORDER BY FIELD (id_truc, 1, 3, 5, 8)
Ce qui donne en requête SPIP :<BOUCLE_t(TRUCS){id_truc IN 1, 3, 5, 8}{par FIELD (id_truc, 1, 3, 5, 8)}>
ça fonctionne très bien comme ça...
...sauf qu’avec la valeur de la liste en dynamique (#SET/ #GET) ça ne veut rien savoir (même avec tous les bricolages possibles...) Dommage ! :-(
John’s Observation Loft : Adding or removing individual SQL modes in MySQL’s sql_mode variable
▻http://johnemb.blogspot.com/2014/09/adding-or-removing-individual-sql-modes.html
La syntaxe des requêtes MySQL pour ajouter/modifier/supprimer à la volée des valeurs aux sql_mode (défini dans la configuration du serveur)
#MariaDB #Memory Allocation - MariaDB Knowledge Base
▻https://mariadb.com/kb/en/library/mariadb-memory-allocation
Allocating RAM for MySQL - The short answer
If using just MyISAM, set key_buffer_size to 20% of available RAM. (Plus innodb_buffer_pool_size=0)
If using just InnoDB, set innodb_buffer_pool_size to 70% of available RAM. (Plus key_buffer_size = 10M, small, but not zero.)
Rule of thumb for tuning mysql:
– Start with released copy of my.cnf / my.ini.
– Change key_buffer_size and innodb_buffer_pool_size according to engine usage and RAM.
– Slow queries can usually be ’fixed’ via indexes, schema changes, or SELECT changes, not by tuning.
– Don’t get carried away with the query cache until you understand what it can and cannot do.
– Don’t change anything else unless you run into trouble (eg, max connections).
– Be sure the changes are under the [mysqld] section, not some other section.
The 20%/70% assumes you have at least 4GB of RAM. If you have a tiny antique, or a tiny VM, then those percentages are too high.
Now for the gory details. (NDB Cluster is not discussed here.)
How to change a MySQL database’s table prefix | Rich Jenks
▻https://richjenks.com/change-mysql-database-table-prefix
The output will be a series of SQL queries that will rename the tables for you
❝SET @database = "database_name";
SET @old_prefix = "old_prefix_";
SET @new_prefix = "new_prefix_";
SELECT
concat(
"RENAME TABLE ",
TABLE_NAME,
" TO ",
replace(TABLE_NAME, @old_prefix, @new_prefix),
';'
) AS "SQL"
FROM information_schema.TABLES WHERE TABLE_SCHEMA = @database;❞
Using #docker To Run #mysql Server In Your Development Environment
▻https://hackernoon.com/using-docker-to-run-mysql-server-in-your-development-environment-e32d523
Today I’m going to show you how can you can use Docker to run MySQL Server in your development environment. Using Docker to run MySQL is much easier than manually installing it and for when you have multiple instances of MySQL running at the same time. I’m assuming that you already have Docker Community Edtion install on your machine. If you do not have Docker installed follow the instruction here for your operating system.Install MySQL Using DockerIf you search online for how to install MySQL using Docker, you will see many different examples on how to do this. I’m going to show how to do it so that you can connect to the database from your IDE and from the applications you are developing.Type this command at the terminal:$ docker run -p 3306:3306 —name hb-mysql-example -e (...)
Impact on Insert Performance — Primary Keys in #mysql
▻https://hackernoon.com/impact-on-insert-performance-primary-keys-in-mysql-590ecf8ba67?source=rs
Impact on Insert Performance — Primary Keys in MySQLThe importance of Primary Keys in MySQLNote: I have used MySQL and it’s storage engine #innodb interchangeably.A couple of days ago, someone at work asked me whether inserts are faster in a table when there’s a primary key present in the table. My immediate answer was that they were faster. I had some idea about this from experience and previous reading but didn’t remember the reason why it was so.For a business user unaware about InnoDB internals or InnoDB’s existence, it might seem perfectly intuitive to assume that inserts will be faster without a primary key because a primary key is an additional constraint on the table, i.e., MySQL has to check for uniqueness, nullability. When it comes to InnoDB, that is not the case. Also, a table (...)
Adding #elasticsearch To Legacy Application Using #logstash
▻https://hackernoon.com/adding-elasticsearch-to-legacy-application-using-logstash-98c19ac2f0fb?s
ElasticSearch has created a boom in the market with its ability to store, scaling, performing full-text search and analytics on the humongous amount of data in near real time. ElasticSearch is open source search and analytics engine generally used in applications which have complex search features.In this tutorial, we are going to consider a scenario where we wish to add ElasticSearch to our legacy application as a Secondary Data Source. Say suppose we have a legacy application which uses #mysql database and now we have a requirement where we need to have a heavy search and we have decided to move to ElasticSearch. My idea here would still stick to Relational Databases as a Primary Data Store and use ElasticSearch as a Secondary Data Store for the requirements where we need to have the (...)
#sharding writes with #mysql and increment offsets
▻https://hackernoon.com/sharding-writes-with-mysql-and-increment-offsets-48df32379563?source=rss
Sharding Writes with MySQL and Increment OffsetsTL;DRwe shard writes and avoid conflicts using an increment offset on PKseach #data center contains local write serverswe use multi-source replication on read servers so they contain all the data from all data centers, partitioned by monthAt the beginning, things were simpleWe were using MySQL: one master for writing, and a few slaves for reading. Besides replication lag (more on that later), it worked fine. And then you scale. And then it fails! The master is a SPOF — “Single Point Of Failure”.Whether the process failed, the VM crashed, or we had to do maintenance on the server, having only one “master” to write to meant that the service was down for minutes. And this was not acceptable.The first thing we implemented was sharding based on the (...)
#activerecord on #mysql— Iterating over large tables with conditions
▻https://hackernoon.com/activerecord-on-mysql-iterating-over-large-tables-with-conditions-453bd8
In this article, I’m going to demonstrate performance differences between two ways of iterating over the records in a MySQL database table with millions of records. In a high volume analytics system, tables with millions of records are quite common and iterating over the full table or a subset of these tables becomes often necessary — whether it’s to perform computations, run a migration, or create parallelized background jobs on the records. At AirPR, we have many database tables with 100s of millions of records, and it becomes important to write efficient code for iterations because there is often an order of magnitude difference between a good and not-so-good approach.Find Each MethodThe standard approach provided natively by ActiveRecord is the find_eachmethod.For the purposes of this (...)
MySQL — Wikilivres
▻https://fr.wikibooks.org/wiki/MySQL
Documentation de MySQL en français (puisque Oracle a supprimée le français de la doc officielle...)
Elephant in the Room : #database #backup
▻https://hackernoon.com/elephant-in-the-room-database-backup-574da50e6d88?source=rss----3a8144ea
Photo by Alexandre ChambonWe’ve seen it many times. Yet it sill occurs on a regular basis, as if there’s no permanent cure for this chronic problem.About 9 years ago, Sidekick, a popular T-Mobile smartphone at the time, lost all of user data in the cloud, leaving 800,000 users without access to their own personal data such as email, notes, contacts, calendars and photos.Later on, Microsoft decided to discontinue the cloud service and eventually, sales of the device.As a result, a massive business and its ecosystem ceased to exist. It was the biggest disaster in cloud computing history.GitLab also suffered from broken recovery procedures when an engineer had accidentally deleted a directory on the wrong server, by running rm -rf /important-data on their primary database.Side note: Kudos to (...)
Showdown : #mysql 8 vs PostgreSQL 10
▻https://hackernoon.com/showdown-mysql-8-vs-postgresql-10-3fe23be5c19e?source=rss----3a8144eabfe
Photo by David MarcuNow that MySQL 8 and PostgreSQL 10 are out, it’s a good time to revisit how the two major open source relational databases compete against each other.Before these versions, the general perception has been that while #postgres is superior in feature sets and its pedigree, MySQL is more battle tested at scale with massive concurrent reads /writes.But with the latest releases, the gap between the two has gotten significantly narrowed.Feature ComparisonLet’s take a look at the “trendy” features that we all love to talk about.It used to be easy to say that MySQL works best for online transactions, and PostgreSQL works best for analytical processes. But not anymore.Common Table Expressions (CTEs) and window functions have been the main reason to choose PostgreSQL. But now, (...)
bash - How to output MySQL query results in CSV format ? - Stack Overflow
▻https://stackoverflow.com/questions/356578/how-to-output-mysql-query-results-in-csv-format#356605
Plein de méthodes pour sortir le résultat d’une requête MySQL dans un fichier CSV (même si le serveur est configuré en mode paranoïaque (—secure-file-priv option) qui l’empêche de sortir le résultat dans un fichier.
On peut aussi utiliser (élégamment !) pager : ▻https://www.zen-tech.info/2010/02/enregistrer-resultat-dun-requete-mysql-dans-un-fichier
On peut utiliser un lien symbolique aussi. Ou un « mount bind », pour monter un répertoire dans un autre répertoire. De la sorte, pas la peine de modifier la configuration de MySQL.
Et on évite en général de copier les fichiers de MySQL dans un répertoire d’un autre utilisateur. On peut avantageusement créer un nouveau disque dans la machine virtuelle, et le monter sur /srv
Convertir une base de données SQLite vers MySQL/MariaDB - PAB’s blog
▻https://blog.bandinelli.net/index.php?post/2014/03/27/sqlite3-to-mysql
LE script miraculeux pour la conversion sqlite > sql
NB : ne pas oublier de faire un rechercher/remplacer pour virer les chaînes COLLATE NOCASE et COLLATE BINARY avant de lancer l’import du fichier SQL obtenu.
...et ne pas oublier de remettre le AUTO_INCREMENT sur tous les champs id_<objet> qui sont les clés primaire des tables spip_<objet>
#conversion #sqlite #mysql #mariadb #python #utilitaire #SPIP
Sinon, tu as aussi LE plugin miraculeux :
►https://contrib.spip.net/Fusion-de-SPIP
Si vous avez créé votre site avec une base de données en sqlite, et que pour diverses raisons vous voulez le passer en mysql, il n’y a pas vraiment d’outil le permettant facilement.
Mais avec ce plugin, c’est possible et même très simple.
heu... ça ne fonctionne pas si bien que ça si tu as ajouté des champs extras...
Ben ça fonctionne bien pour ceux qui l’utilisent, et sinon faut pas hésiter à proposer des améliorations :)
Le principe, actuellement, c’est de comparer les schémas des deux bases et de n’importer que ce qui existe sur l’hôte (tables et champs).
Mais on peut imaginer un fonctionnement alternatif ou les champs et tables de la source soient créés et importés.
A moins que tu n’aies rencontré un problème encore différent ?
#macOS 10.12 Sierra #Apache Setup : Multiple #PHP Versions | Grav
▻https://getgrav.org/blog/macos-sierra-apache-multiple-php-versions
Partie 2: MySQL, Virtual Hosts, APC cache, YAML, et Xdebug
▻https://getgrav.org/blog/macos-sierra-apache-mysql-vhost-apc
Partie 3: SSL
▻https://getgrav.org/blog/macos-sierra-apache-ssl
mysql - #1071 - Specified key was too long ; max key length is 767 bytes - Stack Overflow
▻https://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes/38675910#38675910
Les 2 modifications à apporter à une table MySQL (innodb) pour la rendre compatible (format Baracuda) avec les clés à rallonge de Moodle et éviter l’erreur « 1071 Specified key was too long ; max key length is 767 bytes »
What’s the difference between utf8 and utf8mb4 in MySQL?
▻http://www.eversql.com/mysql-utf8-vs-utf8mb4-whats-the-difference-between-utf8-and-utf8mb4
“If you’re using MySQL in any flavor (MySQL, MariaDB, PerconaDB, etc.), make sure you know your encodings. I would recommend anyone to set the MySQL encoding to utf8mb4. Never use utf8 in MySQL, there is no good reason to do that (unless you like tracing encoding related bugs).”
#mysqldumpslow - MariaDB Knowledge Base
▻https://mariadb.com/kb/en/mariadb/mysqldumpslow
mysqldumpslow is a tool to examine the #slow_query_log.
It parses the slow query log files, printing a summary result. Normally, mysqldumpslow groups queries that are similar except for the particular values of number and string data values. It “abstracts” these values to N and ´S´ when displaying summary output. The -a and -n options can be used to modify value abstracting behavior.
Installer Apache, MySQL et PHP sous OSX sans passer par MAMP - Elliptips : Elliptips
▻http://elliptips.info/installer-apache-mysql-et-php-sous-osx-sans-passer-par-mamp
Venant tout juste de changer de Mac, je découvre Mountain Lion et je me suis dit qu’il était temps de mettre un peu d’ordre dans mon environnement de développement. J’avais l’habitude d’utiliser MAMP pour le développement web, car celui-ci a l’avantage indéniable de fournir un package prêt à l’emploi, le tout en un clic. Revers de la médaille, il nous rend tributaire des versions de logiciels qu’il propose et s’intègre parfois mal avec d’autres environnements. Ainsi, nous verrons dans ce tutoriel comment mettre en place un stack AMP (#Apache, #MySQL et #PHP) sous Mac #OSX simplement et rapidement, le tout en utilisant au maximum les outils déjà intégrés au système.
Relancer mysql
brew services restart mysqlRelancer apache
sudo apachectl gracefultail -F /var/log/apache2/error_log
Détail install #apache sur osx
Vérifier que tout va bien : apachectl configtest
En cas de soucis voir : ▻https://apple.stackexchange.com/questions/211015/el-capitan-apache-error-message-ah00526
Config des modules apache : sudo vim /etc/apache2/httpd.conf
Décommenter
LoadModule php5_module libexec/apache2/libphp5.so
LoadModule userdir_module libexec/apache2/mod_userdir.so
Include /private/etc/apache2/extra/httpd-userdir.confActiver les préférences utilisateurs sudo vim /etc/apache2/extra/httpd-userdir.conf
Décommenter
Include /private/etc/apache2/users/*.confDéclarer un fichier de configuration utilisateur sudo vim /etc/apache2/users/USERNAME.conf qui inclut des vhosts persos dans placés dans /Sites de manière à les changer facilement.
Ajouter
Include /Users/USER/Sites/httpd-vhosts.confDéclarer les vhosts persos dans /Users/USER/Sites/httpd-vhosts.conf
Ajouter
<Directory "/Users/USER/Sites/">
AllowOverride All
Options Indexes MultiViews FollowSymLinks
Require all granted
</Directory>
<VirtualHost *:80>
ServerName localhost
ServerAlias local.dev
DocumentRoot "/Users/USER/Sites"
</VirtualHost>
<VirtualHost *:80>
ServerName spip
ServerAlias spip.local
DocumentRoot "/Users/USER/Sites/spip"
</VirtualHost>Note : plutôt que de passer par les httpd-userdir.conf peut-être vaut-il mieux passer par Include /private/etc/apache2/extra/httpd-vhosts.conf car en fait on a pas vraimment plusieurs utilisateurs sur la machine...
Voir : ▻https://coolestguidesontheplanet.com/set-virtual-hosts-apache-mac-osx-10-10-yosemite
Attention à la version d’apache pour déclarer le fichier : ▻https://coolestguidesontheplanet.com/forbidden-403-you-dont-have-permission-to-access-username-
Brancher le tout sur l’internet local : sudo vim /etc/hosts
Ajouter
127.0.0.1 localhost
127.0.0.1 spip spip.localVoir comment apache comprend sa conf : httpd -S
Régler php :
cd /etc
sudo cp php.ini.default php.ini
sudo chmod ug+w php.ini
sudo chgrp staff php.iniConfigurer php sudo vim /etc/php.ini
[Date]
; Defines the default timezone used by the date functions
; http://php.net/date.timezone
date.timezone = Europe/Paris Si vous avez un accès à un disque avec votre user et votre groupe, mais pas apache, vous pouvez donner à apache votre user et votre group
sudo vim /etc/apache2/httpd.conf
#User _www
#Group _www
User vincent
Group staff
Mysql
brew install mysql
brew services list # pour démarrer mysql
mysqladmin -u root password 'yourpassword'vim ~/.my.cnfAjouter
[client]
user=root
password=root
default-character-set=utf8mb4Régler un bug
sudo mkdir /var/mysql
sudo ln -s /tmp/mysql.sock /var/mysql/mysql.socken cas de soucis de mot de passe root
mysqladmin -u root -p password
When it asks for a password, not not enter anything, just hit enter. It will then ask you to enter new password, then confirm. Finished.
la même en propre et clair : ▻https://github.com/BoOz/terminal/blob/master/APACHE_MYSQL_PHP.md
Perte du mot de passe root #MySQL / #MariaDB
▻http://www.dsfc.net/logiciel-libre/mysql-logiciel-libre/perte-mot-de-passe-root-mysql-mariadb
Il n’y a pas de lieu de paniquer lorsque vous avez perdu ou oublié le mot de passe associé au compte root de votre instance MySQL / MariaDB !
#Formateur_MariaDB #Formateur_MySQL #Formateur_SQL #Formation_MariaDB #Formation_MySQL #SQL