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

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]