Forum Moderators: open
I have this tables, wich work in a join to get the data from 2 different databases, here is my query:
SELECT SQL_CACHE HIGH_PRIORITY ph.holecards, ph.handid, ph.submitdate, ph.potsize, ph.gametype, ph.bettype, ph.blinds, ph.room, ph.listed, u.naam AS username, ph.views, u.id AS userid, p.replies, pr.name AS sitename FROM pokerhand ph INNER JOIN tournament_hands th ON ph.handid = th.id INNER JOIN poker_liquiduser.user u ON u.id = ph.userid INNER JOIN posts p ON p.id = ph.postid INNER JOIN pokerhand_pokerroom pr ON pr.roomid = ph.room
INNER JOIN pokerhand_blinds bl ON bl.blindid = ph.blinds WHERE th.status = '1' AND ph.listed = '1' AND ph.deleted = '0' ORDER BY ph.handid DESC LIMIT 0, 50
`pokerhand` is the huge table , it has indexed the fields : `handid`, `listed` and `deleted`.,
`user` is a table from another database.
`tournament_hands` has around 18k records, and both of its fields `id` and `status` are indexed.
`pokerroom`only have a few records both of its fields are indexed too.. `id` and `roomname` , the same for the table `blinds`.
Anyway here is the structure of the tables involved.
CREATE TABLE IF NOT EXISTS `pokerhand` (
`handid` int(11) NOT NULL auto_increment,
`comments` int(11) NOT NULL default '0',
`postid` int(11) default NULL,
`userid` int(11) NOT NULL default '0',
`handcontent` longtext NOT NULL,
`room` tinyint(4) default NULL,
`listed` enum('0','1') NOT NULL default '1',
`submitdate` int(11) default NULL,
`holecards` varchar(255) default NULL,
`deleted` enum('0','1') NOT NULL default '0',
`views` int(11) NOT NULL default '0',
`bettype` int(11) default '0',
`gametype` int(11) default '0',
`potsize` decimal(10,2) default '0.00',
`blinds` int(11) default NULL,
`orig_hand` longtext,
`sb` decimal(9,2) default NULL,
`bb` decimal(9,2) default NULL,
`roomname` tinyint(4) default NULL,
`views2` int(11) NOT NULL default '0',
`tournament_hand` int(4) NOT NULL,
PRIMARY KEY (`handid`),
KEY `userid` (`userid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=735556 ;
CREATE TABLE IF NOT EXISTS `pokerhand_blinds` (
`blindid` int(11) NOT NULL auto_increment,
`sb` decimal(9,2) NOT NULL default '0.00',
`bb` decimal(9,2) NOT NULL default '0.00',
PRIMARY KEY (`blindid`),
UNIQUE KEY `sb` (`sb`,`bb`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=691 ;
CREATE TABLE IF NOT EXISTS `pokerhand_pokerroom` (
`roomid` int(11) NOT NULL auto_increment,
`name` varchar(50) NOT NULL default '',
PRIMARY KEY (`roomid`),
UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=36 ;
CREATE TABLE IF NOT EXISTS `tournament_hands` (
`id` int(11) NOT NULL,
`status` enum('1','0') NOT NULL default '1',
PRIMARY KEY (`id`),
KEY `status` (`status`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `user` (
`id` int(11) NOT NULL auto_increment,
`naam` varchar(20) default NULL,
`adres` varchar(20) default NULL,
`quote` varchar(255) default NULL,
`pass` varchar(50) default NULL,
`datum` datetime NOT NULL default '0000-00-00 00:00:00',
`avatar` varchar(35) default NULL,
`usernote` longtext,
`modnotes_topicid` int(11) NOT NULL default '0',
`background` varchar(20) NOT NULL default 'FDFDFD',
`posts` int(11) default '0',
`bet` enum('0','1') default '0',
`mail_login` varchar(30) NOT NULL default '',
`show_last` enum('0','1') NOT NULL default '0',
`register_ip` varchar(15) default NULL,
`email` varchar(64) default NULL,
`bnet_nick` varchar(20) default NULL,
`showbud` char(1) default '1',
`icon_race` int(3) default '1',
`user_icon` varchar(64) default '',
`bday` varchar(10) default NULL,
`user_color` varchar(6) default '0',
`mood` tinyint(4) default '0',
`current_song` varchar(60) default '',
`activated` enum('0','1') NOT NULL default '0',
`newsletter` enum('0','1') NOT NULL default '0',
`mod_noted` enum('0','1') NOT NULL default '0',
`party_ref` int(11) default NULL,
`titan_ref` varchar(50) default NULL,
`qualityposter` enum('0','1') NOT NULL default '0',
`mod_image` varchar(30) default NULL,
`phoneverified` tinyint(1) default NULL,
`stars_alias` varchar(20) default NULL,
`siteid` int(11) NOT NULL default '1',
`pref_game` enum('0','1','2','3') default '0',
`pref_bet` enum('0','1','2','3') default '0',
`pref_limit` decimal(9,2) default '0.00',
`pref_listed` enum('0','1') default '1',
`pref_hide` enum('0','1') default '0',
`pref_tournament_hands` enum('1','0') NOT NULL,
`pref_color_deck` enum('1','0') default NULL,
PRIMARY KEY (`id`),
KEY `id` (`activated`),
KEY `siteid` (`siteid`),
KEY `naam` (`naam`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=66432 ;
The query takes soo long to get the results, any idea of how i can improve this?
P.s: i cant make a substancial change to the structure since there are many data involved.
Thanks in advance
You should also make sure you index any field that you are joining on in select statements.
If you do have indexes on all these fields then I would try creating a view using the select statement you have as the definition for the view. Then query the view instead of the tables.
You can also find where the bottle necks are if you run the statement with Explain.
example:
explain SELECT SQL_CACHE HIGH_PRIORITY ph.holecards, ph.handid,....... ORDER BY ph.handid DESC LIMIT 0, 50
