The speed of such a statement is directly dependent on the factors outlined below.įirst off, the structure of our tables is important because queries involving columns that were not “cared for” (indexed, etc.) properly will be slower than they should be. In other words, when MySQL is ordered to modify a table and an ALTER TABLE statement is run, MySQL makes a copy of the table that is being modified, inserts the data that we currently have inside of our table into it, performs all of the required operations there, and copies the data back into our table – only then we see the results.ĪLTER TABLE might work a little differently if we are not using InnoDB as our primary storage engine of choice – MyISAM will not show the rows that are being inserted into table until all operations have been completed, but nonetheless, the core premise remains the same. ALTER TABLE performs all of its operations on a copy of the table, and not on the table itself. The speed of ALTER TABLE statements is, obviously, directly dependent on the size of the tables or columns we want to modify – however, there’s one caveat. On its end though, ALTER TABLE is mostly used when indexes are involved – when developers need to add indexes, they change the structure of their tables, and that's where ALTER TABLE comes into play once again. The things that ALTER TABLE can do are numerous – ALTER TABLE can be used to change names of tables or columns, or add or modify columns as well. Simply put, ALTER TABLE is a query in MySQL that allows us to modify (or ALTER) the structure of a table. If you’ve ever dug deeper into the world of MySQL though, you must know that there is one more query that is very important for both database administrators and developers – that query is ALTER TABLE. SELECT queries allow us to read data, INSERT queries allow us to insert data into a given database, UPDATE queries allow us to update data for certain rows or for the entire table if we wish, DELETE queries let us delete records from a table, etc. Chances are, you know them like your five fingers. If you frequently find yourself immersed into the MySQL world, there’s probably no need to explain to you what SQL queries are and what they do.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |