| Speed up large table SQL select query. [message #3522] |
Thu, 11 September 2008 13:06  |
 llue
Messages: 1
Registered: September 2008 |
Junior Member |
|
|
Hi!
I got a very large SQL table (50 million rows). The simple select query is running repeatedly and is identified a bottleneck.
Here is the detail info:
1. table
userinfo | CREATE TABLE `userinfo` (
`uid` int(11) NOT NULL auto_increment,
`login` varchar(45) NOT NULL,
`domain_id` int(11) NOT NULL,
`fname` varchar(40) default NULL,
`lname` varchar(20) default NULL,
`address` varchar(40) default NULL,
`city` varchar(20) default NULL,
`state` varchar(20) default NULL,
`zip` varchar(10) default NULL,
`country` varchar(10) default NULL,
`sex` char(1) default NULL,
`phone` varchar(20) default NULL,
PRIMARY KEY (`uid`),
UNIQUE KEY `email` (`login`,`domain_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
2. the select statement
mysql> explain select uid from userinfo where login = 'name' and domain_id = 1;
+----+-------------+----------+------+---------------+------ -+---------+-------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------ -+---------+-------------+------+--------------------------+
| 1 | SIMPLE | userinfo | ref | email | email | 51 | const,const | 1 | Using where; Using index |
+----+-------------+----------+------+---------------+------ -+---------+-------------+------+--------------------------+
1 row in set (0.00 sec)
We are trying to insert data into table and the select statement is used to look up the uid according to login + domain_id;
By increasing the system parameter like innodb_buffer_pool_size doesn't help much. The cache hit rate is extremely low.
Are there any way to speed up the query? By optimizing the table, fine tune mysql?
Thanks
-ll
|
|
|
| Re: Speed up large table SQL select query. [message #3538 is a reply to message #3522 ] |
Thu, 18 September 2008 09:53  |
Carsten_H.
Messages: 9
Registered: August 2006 |
Junior Member |
|
|
If you just need the uid, try splitting the table perhaps?
CREATE TABLE `userinfo` (
`uid` int(11) NOT NULL auto_increment,
`login` varchar(45) NOT NULL,
`domain_id` int(11) NOT NULL,
PRIMARY KEY (`uid`),
UNIQUE KEY `email` (`login`,`domain_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `userdata` (
`uid` int(11) NOT NULL,
`fname` varchar(40) default NULL,
`lname` varchar(20) default NULL,
`address` varchar(40) default NULL,
`city` varchar(20) default NULL,
`state` varchar(20) default NULL,
`zip` varchar(10) default NULL,
`country` varchar(10) default NULL,
`sex` char(1) default NULL,
`phone` varchar(20) default NULL,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Second: look which is the highest value for domain_id, perhaps you can use a SMALLINT or even TINYINT instead of INT. And set uid AND domain_id to UNSIGNED if you don't have any negative values.
|
|
|
|
| Re: Speed up large table SQL select query. [message #3590 is a reply to message #3522 ] |
Sun, 28 September 2008 20:29 |
MarkRose
Messages: 94
Registered: January 2008 |
Member |
|
|
|
That scares me, because I'm writing a social media application that will have a practically identical query.
|
|
|
Members
Search
Help
Register
Login
Home
