Alles klar...! ;-)
SHOW CREATE... :
Code:
mysql> SHOW CREATE TABLE ways;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ways | CREATE TABLE `ways` (
`id` int(11) NOT NULL,
`maxlat` double NOT NULL,
`maxlon` double NOT NULL,
`minlat` double NOT NULL,
`minlon` double NOT NULL,
`quicknodes` longtext NOT NULL,
`quickamenity` varchar(11) NOT NULL,
`quickleisure` varchar(11) NOT NULL,
`quickhighway` varchar(11) NOT NULL,
`quickbuilding` varchar(11) NOT NULL,
`quicklanduse` varchar(11) NOT NULL,
`quickname` varchar(11) NOT NULL,
`quicklevel` int(11) NOT NULL,
`optimizernodes` int(11) NOT NULL,
`optimized` int(11) DEFAULT NULL,
`visible` tinyint(4) DEFAULT NULL,
`user` varchar(50) DEFAULT NULL,
`timestamp` datetime DEFAULT NULL,
KEY `lat` (`minlat`,`maxlat`),
KEY `lon` (`minlon`,`maxlon`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
EXPLAIN SLELECT... :
Code:
mysql> EXPLAIN SELECT * FROM ways WHERE ((quickamenity='parking') OR (quickhighway<>'') OR (quicklanduse='forest') OR (quickbuilding='yes')) AND maxlon>7.61655923529412 AND minlon<7.63538276470588 AND maxlat>51.9546756470588 AND minlat<51.9664403529412 ORDER BY quicklevel ASC;
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+
| 1 | SIMPLE | ways | ALL | lat,lon | NULL | NULL | NULL | 229509 | Using where; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+
1 row in set (0.03 sec)
Die Buffer-Abfrage:
Code:
mysql> SHOW GLOBAL VARIABLES LIKE '%buffer%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_size | 16777216 |
| innodb_log_buffer_size | 8388608 |
| join_buffer_size | 131072 |
| key_buffer_size | 16777216 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 8192 |
| preload_buffer_size | 32768 |
| read_buffer_size | 262144 |
| read_rnd_buffer_size | 524288 |
| sort_buffer_size | 524288 |
| sql_buffer_result | OFF |
+-------------------------+----------+
12 rows in set (0.00 sec)
Das ganze läuft lokal und mein Rechner hat 4Gb RAM zur Verfügung.
Hoffe, die Infos sind okay.
Schönen Gruß!