Deleting a table
Deleting a table is performed in 2 steps internally:
- Table is cleared (similar to TRUNCATE)
- All table files are removed from the table folder. All the external table files that were used by the table (such as wordforms, extensions or stopwords) are also deleted. Note that these external files are copied to the table folder when
CREATE TABLE
is used, so the original files specified inCREATE TABLE
will not be deleted.
Deleting a table is possible only when the server is running in the RT mode. It is possible to delete RT tables, PQ tables and distributed tables.
- SQL
- JSON
- PHP
- Python
- javascript
- Java
SQL JSON PHP Python javascript Java
DROP TABLE products;
POST /cli -d "DROP TABLE products"
$params = [ 'index' => 'products' ];
$response = $client->indices()->drop($params);
utilsApi.sql('DROP TABLE products')
res = await utilsApi.sql('DROP TABLE products');
sqlresult = utilsApi.sql("DROP TABLE products");
Response
Query OK, 0 rows affected (0.02 sec)
{
"total":0,
"error":"",
"warning":""
}
Array
(
[total] => 0
[error] =>
[warning] =>
)
{u'error': u'', u'total': 0, u'warning': u''}
{"total":0,"error":"","warning":""}
{total=0, error=, warning=}
Here is the syntax of the DROP TABLE
statement in SQL:
DROP TABLE [IF EXISTS] index_name
When deleting a table via SQL, adding IF EXISTS
can be used to delete the table only if it exists. If you try to delete a non-existing table with the IF EXISTS
option, nothing happens.
When deleting a table via PHP, you can add an optional silent
parameter which works the same as IF EXISTS
.
- SQL
- JSON
- PHP
- Python
- javascript
- Java
SQL JSON PHP Python javascript Java
DROP TABLE IF EXISTS products;
POST /cli -d "DROP TABLE IF EXISTS products"
$params =
[
'index' => 'products',
'body' => ['silent' => true]
];
$client->indices()->drop($params);
utilsApi.sql('DROP TABLE IF EXISTS products')
res = await utilsApi.sql('DROP TABLE IF EXISTS products');
sqlresult = utilsApi.sql("DROP TABLE IF EXISTS products");
Response
{u'error': u'', u'total': 0, u'warning': u''}
{"total":0,"error":"","warning":""}
{total=0, error=, warning=}
Emptying a table
The table can be emptied with a TRUNCATE TABLE
SQL statement or with a truncate()
PHP client function.
Here is the syntax for the SQL statement:
TRUNCATE TABLE index_name [WITH RECONFIGURE]
When this statement is executed, it clears the RT table completely. It disposes the in-memory data, unlinks all the table data files, and releases the associated binary logs.
A table can also be emptied with DELETE FROM index WHERE id>0
, but it’s not recommended as it’s much slower than TRUNCATE
.
- SQL
- JSON
- PHP
- Python
- javascript
- Java
SQL JSON PHP Python javascript Java
TRUNCATE TABLE products;
POST /cli -d "TRUNCATE TABLE products"
$params = [ 'index' => 'products' ];
$response = $client->indices()->truncate($params);
utilsApi.sql('TRUNCATE TABLE products')
res = await utilsApi.sql('TRUNCATE TABLE products');
utilsApi.sql("TRUNCATE TABLE products");
Response
Query OK, 0 rows affected (0.02 sec)
{
"total":0,
"error":"",
"warning":""
}
Array(
[total] => 0
[error] =>
[warning] =>
)
{u'error': u'', u'total': 0, u'warning': u''}
{"total":0,"error":"","warning":""}
{total=0, error=, warning=}
One of the possible uses of this command is before attaching a table.
When RECONFIGURE
option is used new tokenization, morphology, and other text processing settings specified in the config take effect after the table gets cleared. In case the schema declaration in config is different from the table schema the new schema from config got applied after table get cleared.
With this option clearing and reconfiguring a table becomes one atomic operation.
- SQL
- HTTP
- PHP
- Python
- javascript
- Java
SQL HTTP PHP Python javascript Java
TRUNCATE TABLE products with reconfigure;
POST /cli -d "TRUNCATE TABLE products with reconfigure"
$params = [ 'index' => 'products', 'with' => 'reconfigure' ];
$response = $client->indices()->truncate($params);
utilsApi.sql('TRUNCATE TABLE products WITH RECONFIGURE')
res = await utilsApi.sql('TRUNCATE TABLE products WITH RECONFIGURE');
utilsApi.sql("TRUNCATE TABLE products WITH RECONFIGURE");
Response
Query OK, 0 rows affected (0.02 sec)
{
"total":0,
"error":"",
"warning":""
}
Array(
[total] => 0
[error] =>
[warning] =>
)
{u'error': u'', u'total': 0, u'warning': u''}
{"total":0,"error":"","warning":""}
{total=0, error=, warning=}
Manticore cluster
Manticore Search is a highly distributed system and consists of all the needed components to allow you build a highly available and scalable setup of a database for search:
- distributed table allowing sharding
- Mirroring for high availability
- Load balancing for high scalability
- Replication for data safety
Manticore Search is extremely flexible in terms of how you setup your cluster, there’s no limitations and it’s up to you how you design it. Just learn the tools mentioned above and use them to achieve your goal.