Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have 2 mysql table:

table t1:

CREATE TABLE IF NOT EXISTS `t1` (
  `id` bigint(20) NOT NULL,
  `url` varchar(255) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `type` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

table t2

CREATE TABLE IF NOT EXISTS `owa_request` (
  `id` bigint(20) NOT NULL,
  `date` int(11) DEFAULT NULL,
  `ip` varchar(15) DEFAULT NULL,
  `d_id` bigint(20) DEFAULT NULL,
  `u_id` bigint(20) DEFAULT NULL,
  `browser` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `d_id` (`d_id`),
  KEY `u_id` (`u_id`),
  KEY `date` (`date`),
  KEY `ip` (`ip_address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Now I want to make such a query:

select 
t1.id,t1.url,t1.type,
t2.d_id,t2.date,t2.ip 
from t1,t2 
where t1.id=t2.d_id and t1.type='article' 
group by t2.ip 
HAVING COUNT(t2.ip)>10

get all the data group by t2.ip and each t2.ip should have at least 10 results.

But the query is very slow, 300k rows data (70MB), cost 12.5290 sec, have I make bad indexes? how to optimize let query under half second? Thanks.

share|improve this question
    
You have a KEY type (page_type) but no page_type column. Can you correct the typo? –  ypercube Mar 14 at 11:43
    
Yes your DDL is wrong ! That is bad. –  Up_One Mar 14 at 12:59
    
I would try adding an index on (ip, d_id) –  ypercube Mar 14 at 17:21
    
@ypercube, page_type should be type some writing mistake. I have added an index on (ip, d_id) but Query took 2.5442 sec, slower than Up_One's answer. –  cj333 Mar 15 at 18:04
    
Have you tried with SELECT t2.ip FROM ... only? The other columns you have currently included in the select list will give you arbitary, indeterminate results because of the group by. –  ypercube Mar 15 at 21:32

1 Answer 1

I think that your type key is the main problem for you ! as you have it set to the max index size in mysql !
Try to see the real need of those 255(767 byte index) that is big for a table with that many rows. And check the distinct values in this column 'type' maybe a partition can be used in order to get rid of unnecessary reads.

share|improve this answer
    
thx, now i use type varchar(100) DEFAULT NULL, cost 1.5050 sec, but still feel slow, 2 tables only with 70MB... I have another innodb table, 9.3GB some query only cost 0.4245 sec. –  cj333 Mar 14 at 17:05
    
Can you run this queries and post the output ? 1-select max(LENGTH(Marks)) as length from t1; 2- select count(DISTINCT(Marks)) from t1; –  Up_One Mar 14 at 18:43
    
select max(LENGTH(type)) as length from t1, 79; select count(DISTINCT(type)) from t1, 14. Thanks. –  cj333 Mar 15 at 18:08
    
ok! now alter your table definition by making your type column to "varchar(80) not null" and create 14 partitions representing those 14 distinct type values ! make this on a diferent replicated table - for test only –  Up_One Mar 17 at 11:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.