PHP, Google and SQL

[karldesign]

This is a pretty dumb question, I think... hopefully I have answered myself, but here goes!

 

I have a site that loops through a database of places to be added on a Google map. Would the site speed decrease as this database grows?

[aschk]

Depends entirely on your SQL + other system factors such as normal server load.

Normally, yes, your SQL will take longer to run (by 100th's of a second) if you are loading up larger datasets each time.

Ideally you should be restricting this dataset to the bare minimum each time you make a call.

Post any SQL you might be using and i'm sure you we can point you in the right direction for optimising it.

[karldesign]

SELECT * FROM tbl_places WHERE int_region = 1 AND int_status = 1 ORDER BY dtm_added DESC

 

Nothing more special than this really.

[cooldude832]

that will result in a linear relationship of speed to size  since you are selecting all rows.  However if u only select a few rows, then no.

[karldesign]

There are 10 cols in this table, I only need 9 every time, will selecting the 9 be quicker?

[aschk]

Okey dokey, looks pretty good. At least you have a where clause.

My advice:

1) make sure you specify your columns (SELECT col1, col2, col3...)

2) Put indexes on your int_region and int_status column.

3) If you expect both of those to be utilised consistently together you could consider a composite key of both; (int_region,int_status)

4) Where possible use unique indexes

5) Make sure you have a primary key defined.

6) If in doubt EXPLAIN <yourquery> is your friend.

[karldesign]

Okey dokey, looks pretty good. At least you have a where clause.

My advice:

1) make sure you specify your columns (SELECT col1, col2, col3...)

2) Put indexes on your int_region and int_status column.

3) If you expect both of those to be utilised consistently together you could consider a composite key of both; (int_region,int_status)

4) Where possible use unique indexes

5) Make sure you have a primary key defined.

6) If in doubt EXPLAIN <yourquery> is your friend.

 

Cheers so far, heres where I am at:

 

1) Is this quicker, even though I'm using 9/10 cols?

2) Not too sure how/what this is?

3) int_region changes often, int_status will always be 1

4) Again, unfamiliar with this

5) Primary key is a unique id

6) Unsure again

[aschk]

1) I heard a rumour a couple of years ago regarding a certain Microsoft product that should you specify all the columns individually instead of using * it would work quicker.

 

2) Indexes are the crux of databases, without them your SQL queries will do full table scans (basically scanning every row), which is a BAD thing. Think about them as pointers to certain segregations of data.

 

3) If you have a column (int_status) that will always be 1 then why do you have that column at all?

 

4) A unique index is an index that can ONLY occur once is the whole table. e.g. you have a row with column `name` and value "joe bloggs". If joe bloggs could only occur once (and the same for all other rows with the name column) then the column `name` should have a unique index.

 

5) Yes a primary key IS a unique id, however not all unique indexes are primary keys.

 

6) EXPLAIN is a SQL command, e.g. EXPLAIN SELECT * FROM tbl_places WHERE int_region = 1 AND int_status = 1 ORDER BY dtm_added DESC. Do it and see for yourself