Re: A Select improvement

[Jon Drukman]

Dan Sashko wrote:
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 < 2
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]

Re: A Select improvement

[Dusan Pavlica]

Hi Dan,
try to create composite index on id, domain and score
ALTER TABLE ADD INDEX indexName (id, domain, score)
Dusan
- Original Message - 
From: "Dan Sashko" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, December 01, 2004 4:26 AM
Subject: A Select improvement


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 < 2
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.

It is very slow considering that the WHERE has to be ran many many times 
with different parameters

 here is table info :
# Table: 'tbl_1'
#
CREATE TABLE `tbl_1` (
 `id` int(10) unsigned NOT NULL auto_increment,
 `domain` varchar(50) NOT NULL default '',
 `data` varchar(200) default '',
 `score` int(11) default NULL,
 PRIMARY KEY  (`id`),
 KEY `score` (`score`),
 KEY `domain` (`domain`),
 FULLTEXT KEY `data` (`data`)
) TYPE=MyISAM;
 heres query info (it remains the same if i remove some WHERE 
clauses except for rows count going up when i do:
table| type | possible_keys | key| key_len 
| ref| rows | Extra |
tbl_1|ref  | score,domain  | domain |  50 
| const  |1 | Using where; Using temporary; Using filesort 
|

---
thank you
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]