Which is better: big SQL statement or bigger db?
I need to have a user input a city and have MySQL pull up any records with that city OR nearby cities (within 10 mi). Which of the following would be the most efficient way to do this: Case A: When a user enters a city, an array of nearby cities is created so that an SQL statement like the following is generated: SELECT * FROM bc_posts WHERE post_citysoundex = 'A265' OR post_citysoundex = 'A415' OR post_citysoundex = 'A453' OR post_citysoundex = 'A430' OR post_citysoundex = 'A624' OR post_citysoundex = 'A350' OR . . . This statement would probably be much larger (upto 150 lines) and would query one table without additional joins. Case B: Here, when a user enters a city, the soundex of it is created and then queries a table that contains every city in the db PLUS all surrounding cities (calculated and inserted with each new city insert). Obviously, here the table would get large while my actual SQL statement is pretty straightforward but would require a join. I'm not sure which of these is the more elegant approach or would scale up much easier. Any input from the DB gurus would be appreciated! Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Which is better: big SQL statement or bigger db?
Hi,
May the following sql statement is more efficient.
SELECT * FROM bc_posts WHERE
post_citysoundex IN('A265','A415',.)
Armand
motorpsychkill wrote:
I need to have a user input a city and have MySQL pull up any records with
that city OR nearby cities (within 10 mi). Which of the following would be
the most efficient way to do this:
Case A:
When a user enters a city, an array of nearby cities is created so that an
SQL statement like the following is generated:
SELECT * FROM bc_posts WHERE
post_citysoundex = 'A265' OR
post_citysoundex = 'A415' OR
post_citysoundex = 'A453' OR
post_citysoundex = 'A430' OR
post_citysoundex = 'A624' OR
post_citysoundex = 'A350' OR
.
.
.
This statement would probably be much larger (upto 150 lines) and would
query one table without additional joins.
Case B:
Here, when a user enters a city, the soundex of it is created and then
queries a table that contains every city in the db PLUS all surrounding
cities (calculated and inserted with each new city insert). Obviously, here
the table would get large while my actual SQL statement is pretty
straightforward but would require a join.
I'm not sure which of these is the more elegant approach or would scale up
much easier. Any input from the DB gurus would be appreciated! Thanks!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Which is better: big SQL statement or bigger db?
* [EMAIL PROTECTED] Case A: ... This statement would probably be much larger (upto 150 lines) and would query one table without additional joins. Case B: Here, when a user enters a city, the soundex of it is created and then queries a table that contains every city in the db PLUS all surrounding cities (calculated and inserted with each new city insert). Obviously, here the table would get large while my actual SQL statement is pretty straightforward but would require a join. I'm not sure which of these is the more elegant approach or would scale up much easier. Any input from the DB gurus would be appreciated! Thanks! Most elegant is case B I would think. Let the database system do the work it is designed for. Scalability and performance in mysql I do not know. What about a test? -- Jon Haugsand, [EMAIL PROTECTED] http://www.norges-bank.no -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
