I wonder...
if MariaDB is like any other SQL, then deleting records or executing a While command to traverse a table for any reason would be loosing a lot of benefits that SQL offers.
When you execute a while loop to check each record for a condition, that information travels from the server to the workstation where the condition is evaluated. If instead you allow the server to execute the condition, you save LOTS of time and network traffic, to say the least. That's why you notice it is a lot faster to re-index on the server than on a workstation and I'm probably not saying anything new to anyone.
On the example in this thread, instead of traversing the complete table checking for eof(), you would do this:
- Code: Select all Expand view
DELETE FROM <tablename> ;
Suppose you want to extract certain records from the table based on certain condition. With our traditional ISAM you would do this:
- Code: Select all Expand view
WHILE !odbf:eof() //or some other condition
if <condition> //another condition
tbl2:Seek( odbf:KeyValue )
if Tbl2:<condition> ...
...
endif
....
endif
odbf:skip()
END
If you leverage the benefits of SQL you would let the server execute that without the need for each record to travel across the wires:
- Code: Select all Expand view
SELECT * FROM <tablename> WHERE <condition>
Or
SELECT * FROM <tbl1> t1
LEFT JOIN <tb2> t2 ON t2.keyField = t1.keyfield
WHERE <condtion>
I say this because I notice people using Maria and MySQL but continue to use ISAM while loosing a great opportunity to start learning SQL.
If Mariadb is an SQL engine, then perhaps we can start asking how to implement some while loop that traverses a table for the equivalent SQL statement.