Hi, anyone has suggestions what changes to make to allow this query to run faster?
SELECT domain FROM tbl_1 WHERE id > 0 and id < 20000 domain = "12.221.190.111" AND score IS NOT Null AND data LIKE "%param=search" GROUP BY domain, data
----------
every one of those WHERE clauses makes the query very slow.
for about 50 million records with 200-900 thousand matching records it takes about two minutes if I only have the straight domain = "some string",
then almost quadriples if I add the data Like "pattern" clause.
LIKE with a leading wildcard (%param=search) is going to kill you every time, because it cannot be indexed. (Indexes start from the left.)
If I were you, I'd add another column called search, make it tinyint(1) not null, and index it. Every time you insert a row with param=search on the end, set the search column to 1. That only solves this specific case however. If you are looking for lots of different patterns, you'll have to think of something else. Possible ideas:
If you're always looking for param=(word) you could put (word) in a separate column and index that.
-jsd-
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
