Bräuchte Hilfe mit... Indizes (?)

[Myribo]

Alles klar...! ;-)

SHOW CREATE... :

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... :

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:

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ß!