It should be a rapidshare links database (which updates with a PHP crawler I
wroted last Saturday).
I would like to change the snippet to title and add another column for the
snippet,
My main queries are INSERT's - a big insert (usually 10-100 links per
insert) in each hour.
My crawler is checking if the link it is about to add is already in the
database with a select query like this:
SELECT `id` FROM `tbl_name` WHERE `unique` = '%_UNIQUE_VARIABLE_%'
create index unique_idx on tbl_name(unique);
mysql might work better with this index:
create index unique_idx on tbl_name(unique, id);
because in some cases it doesn't have to go back to the data file to get
the actual data, it can just read everything from the index.
I'm definiatly not an database scheme expert, I'm looking to optimaize the
table for fast select queries (to check if file is already exists)
And for fast filename-based search (select * from `tbl_name` where
`filename` like '%_WHATEVER_%')
like queries are harder to optimize.
If you put a wildcard at the front:
like '%...%';
the db can't use an index to find it, because you're saying the text can
be anywhere and for that type of search you're best off setting up
fulltext (http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html).
If you don't put a % at the front:
like '...%';
the db use an index to find it (up until the wildcard)..
create index filename_idx on tbl_name(filename);
To understand indexing, check out my article:
http://www.designmagick.com/article/16/PostgreSQL/How-to-index-a-database
(Yes it's on a postgres site but the ideas/understanding work for all
db's - and the commands should even work for mysql).
--
Postgresql & php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
