ANALYZE/ OPTIMISE in MYSQL

[natasha_sharma]

I am sorry if i sound stupid to ask this question can someone please explain me in simple terms what do ANALYZE/ OPTIMISE do ?

 

Do they like refresh the index? Or what?

 

I could not understand the documentation much.

[gizmola]

The answer really depends on the engine, but the basic idea is that analyze looks at key distribution in the table and updates some statistics in the data dictionary so that the query optimizer can make informed decisions in regards to when it should use an index or ignore it.

 

Optimize is basically analyze+ some table restructuring, but this again is highly dependent on the storage engine.  Innodb doesn't typically need analyze, as it has statistical updates baked in.  By the same token the way data is stored in innodb vs myisam is completely different, so there's literally nothing common between engines, and that certainly is true of the other engines that have come along in Maria etc.

[natasha_sharma]

Thankyou Gizmola.

 

Here is the details of my XAMPP MysQL:

 

• Server: 127.0.0.1 via TCP/IP
• Server type: MariaDB
• Server version: 10.1.9-MariaDB - mariadb.org binary distribution
• Protocol version: 10
• User: root@localhost
• Server charset: UTF-8 Unicode (utf8)

 

So it looks like its MariaDB, so now doing ANALYZE and OPTIMIZE will be beneficial for MariaDB unlike for innoDB?