Geek asks and answers

PHP / MySQL - performance considerations - query (select only) 48k rows

I'm currently trying to create a web application that is heavily dependent on zip code data (supplied from OS CodePoint Open ). The zip code database contains 120 tables that break the initial zip code prefix (i.e., SE, WS, B). Inside these tables, there are rows from 11k to 48k with three fields (Postcode, Lat, Lng).

I need the user to be able to log on to the network, enter his zip code, i.e. SE1 1LD, which then selects the table SE and converts the zip code to a lat / lng file.

I do this fine at the PHP level. My concern is ... well, a huge number of lines to be requested, and will he stop my site at a halt?

If there are any methods that I should know about, please let me know .. I have never worked with tables with large numbers!

Thank:)

+3
source share
4 answers

If I understand correctly, there is a table SE, WSone, Betc. A total of 120 tables with the same structure (Postcode, Lat, Lng) .

I strongly suggest you normalize tables.

You can have one table:

postcode( prefix, postcode, lat, lng)

or two:

postcode( prefixid , postcode, lat, lng )

prefix( prefixid, prefix )

The zip code table will be slightly larger than the 11K-48K lines, about 30K x 120 = 3.6M lines, but this will save you time for writing different queries for each prefix and quite complex if, for example, you want to search for latitude and longitude (submit a query that searches 120 tables).

, person, . (-) ?

prefix - postcode, primary key, . 120 :

postcode( postcode, lat, lng )

:

SELECT * 
FROM postode
WHERE postcode = 'SE11LD'


SELECT * 
FROM postode
WHERE postcode LIKE 'SE%'

, .

+1

48K . 48 .:) ( , WHERE), .

LIKE INNER JOINS LEFT JOINs, .

+4

48k mysql , . , .

+4

, . , , :

CREATE TABLE `postcode_geodata` (
`postcode` varchar(8) NOT NULL DEFAULT '',
`x_coord` float NOT NULL DEFAULT '0',
`y_coord` float NOT NULL DEFAULT '0',
UNIQUE KEY `postcode_idx` (`postcode`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

( ) .

, , MySQL - . http://dev.mysql.com/doc/refman/5.1/en/partitioning.html - , , (. ).

, , MySQL slow_query_log (./etc/mysql/my.cnf) , ( "mysqldumpslow", ).

Also try using the "explain" syntax in the MySQL client.

EXPLAIN SELECT a,b,c FROM table WHERE d = 'foo' and e = 'bar'

These steps will help you optimize the database - by determining which indexes are used (or not used) for the query.

Finally, there is a mysqltuner.pl script (see http://mysqltuner.pl ) that helps you select MySQL server parameters (e.g. query cache, memory usage, etc., which will affect I / O and therefore on performance / speed).

0
source

More articles:


All Articles