CODEWITHSUNDEEP
phpdatabasearraysstringperformance
bizzr3
bizzr3

Reputation: 1955

Text proccessing via Mysql Query

I have lot's of URL that must be saved in DB for searching and updating , which solution is better for performance :

1-store url as string in a table that contain one column , and in later get string from db and explode them to an array and use array_search function to find a URL :

stored url : http://site1.com|http://site2.com|....

search routine : 

$is_unique= array_search("$url" ,explode($row['url'] , "|"));

2-insert one row per each URL in db table and use query for search and update.

Upvotes: 2

Views: 143

Answers (1)

iblue
iblue

Reputation: 30404

Let MySQL handle searching. MySQL can make use of indexes, array_search cannot. Just be sure to declare an index on your url column. You can create an index with the following SQL statement:

CREATE INDEX url_index ON your_table_name (url);

If you bulk update your urls, you can wrap your updates in a transaction, which makes it faster, because the index will be re-generated on commit, not on every single update statement.

START TRANSACTION;
[...your update statements go here...]
COMMIT;

Edit: About security, because I have seen it in one of your comments. If a parameter is user supplied, you want to escape your inputs with mysql_real_escape_string():

mysql_query("SELECT * FROM table WHERE url = ('".mysql_real_escape_string($your_url)."'");

For details about security, please read the PHP manual about mysql_real_escape_string() and/or google about SQL injection

Upvotes: 1

Related Questions