speed up the blocks table:
On my development on this system i get one bad aspact.
The blocks table is to slow. This table contains over 3 mio
rows. So its quite slow to search the locId in this table.
Its better to build more small tables. I split the blocks table in
tables with only 30.000 rows. So the executiontime turns from
3 to 8 secounds to 0.0015 secounds. Only by spliting the table.
But how to manage this?
In MySQL its quite easy to split up an big table in small table. For
this we can use a SQL string like this:
CREATE TABLE IF NOT EXISTS blocks_xxx SELECT * FROM blocks LIMIT 0,30000
I will explain the SQL string. CREATE TABLE is the standard SQL command to create
an table. IF NOT EXISTS means that only if the table doesnt exist the table will be created.
blocks_xxx is the new table name. SELECT * FROM blocks dedines the source table and
with LIMIT 0,30000 = LIMIT startpoint,amount of rows. So you geht only 30000 rows
from the first row. You can also make an for loop in PHP or Python to do this job, but
don’t forget to build also an blocks_index table where you store the startip and endipnum.
When you build an automatic script save automatic the startipnum and endipnum of every
blocks_xxx table in the blocks_index table.
Now we have more that 100 tables
blocks
location
blocks_index
blocks_001 to blocks_117
The next step is to build an application to use this source!
