I've a problem with selecting data from 2 big mysql tables, you can find below the queries & tables details
Query 1:
It takes : 394 seconds.
Code:
SELECT SQL_CALC_FOUND_ROWS `table1`.`url`, `table1`.`Title`, `table1`.`Description`, `table2`.`col16`, `table2`.`col17`, `table2`.`col18`, `table2`.`col19`
FROM `table1`, `table2`
WHERE `table1`.`url` = `table2`.`url`
AND `topic` = 'Arts'
LIMIT 0, 10
Query 2:
It takes : 0.09 seconds (which is MUCH BETTER, but the ids doesn't match in all rows, because some URLs are duplicated in table 1, but all urls are unique in table 2 . So, I can't use it)
Code:
SELECT SQL_CALC_FOUND_ROWS `table1`.`url`, `table1`.`Title`, `table1`.`Description`, `table2`.`col16`, `table2`.`col17`, `table2`.`col18`, `table2`.`col19`
FROM `table1`, `table2`
WHERE `table1`.`id` = `table2`.`id`
AND `topic` = 'Arts'
LIMIT 0, 10
Table 1 "3,206,543 rows" :
Code:
`table1` (
`id` int(11) unsigned NOT NULL auto_increment,
`url` varchar(255) character set utf8 collate utf8_bin NOT NULL,
`Title` varchar(1024) character set utf8 collate utf8_bin NOT NULL,
`Description` varchar(4000) character set utf8 collate utf8_bin NOT NULL,
`topic` varchar(1024) character set utf8 collate utf8_bin NOT NULL,
PRIMARY KEY (`id`),
KEY `topic` (`topic`(333)),
KEY `url` (`url`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Table 2 "2,846,092 rows":
Code:
`table2` (
`id` int(10) unsigned NOT NULL auto_increment,
`url` varchar(255) NOT NULL,
`title` varchar(255) default NULL,
`description` varchar(1024) default NULL,
`keywords` varchar(1024) default NULL,
`col1` varchar(1024) default NULL,
`col2` varchar(1024) default NULL,
`col3` varchar(1024) default NULL,
`col4` varchar(50) default NULL,
`col5` mediumint(8) unsigned default NULL,
`col6` int(11) unsigned default NULL,
`col7` int(11) unsigned default NULL,
|
|
|
`col23` int(11) unsigned default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `url` (`url`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
So, what should I do now ?
dBforums
>
how to optimize these 2 tables ?
08-17-10, 22:22
Linear Mode
