I have a table and a VARCHAR column which I want to search on. This is very read-heavy.
I want to do something like:
SELECT id FROM table WHERE domain = ‘sitepoint.com’
I MAY need to do this, at least sometimes:
SELECT id FROM table WHERE domain LIKE ‘%sitepoint.com’
What’s the best table type/related configs for this table?
So when I am doing a domain = ‘xyz.com’, I should still put an index on that varchar text column?
as for inserts - this table data gets recreated daily and no inserts in between
Another solution if you want to search domains and subdomains is have a column where the order of components of the domain are reversed, e.g. com.example.bar.foo.
If you use a wild card at the beginning of your search term no index can be used. On the other hand MyISAM supports FULLTEXT indexes. They may be what you need.
it might also be a case of where the column value can be foo.example.com and bar.example.com
best thing to do would be to have a separate column for the domain itself, i.e. example.com
that way you’ll never have to mess around with LIKE, and the searches will be properly optimized to use an index
which varchar column?
No worries 
I’ve moved this thread to the MySQL forum.
As for your question, I’d say first of all you need to add an index to that VARCHAR column. Unless you also get a lot of inserts and updates on the table, in which case the index might hurt you more than it helps.
As for InnoDB vs MyISAM, if memory serves MyISAM is faster at table scans and index scans than InnoDB, so you should probably go for MyISAM, but I’m not 100% sure.
MySQL - sorry
Which DBMS are we talking about here? MySQL? PostgreSQL? Something else?
when you write " LIKE ‘%sitepoint.com’ " do you mean it may be www.sitepoint.com or http://www.sitepoint.com
might be better to use a regex in your server-side language to remove the www and the http:// before running the query?
Of course, I may have missed the ball.
bazz