# Speed up large table SQL select query.

**URL:** <https://forums.percona.com/t/speed-up-large-table-sql-select-query/920>\
**Category:** Other MySQL® Questions\
**Created:** [September 11, 2008, 11:36am UTC](https://forums.percona.com/t/speed-up-large-table-sql-select-query/920 "2008-09-11T11:36:34Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![llue](https://avatars.discourse-cdn.com/v4/letter/l/9dc877/32.png) [@llue](https://forums.percona.com/u/llue)\
**Post date:** [September 11, 2008, 11:36am UTC](https://forums.percona.com/t/speed-up-large-table-sql-select-query/920/1 "2008-09-11T11:36:34Z")

</div>

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;

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

---

<div class="post-metadata">

**Author:** ![Carsten\_H](https://avatars.discourse-cdn.com/v4/letter/c/d26b3c/32.png) [@Carsten\_H](https://forums.percona.com/u/Carsten_H)\
**Post date:** [September 18, 2008, 8:23am UTC](https://forums.percona.com/t/speed-up-large-table-sql-select-query/920/2 "2008-09-18T08:23:44Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![artur8ur](https://avatars.discourse-cdn.com/v4/letter/a/9f8e36/32.png) [@artur8ur](https://forums.percona.com/u/artur8ur)\
**Post date:** [September 28, 2008, 3:16pm UTC](https://forums.percona.com/t/speed-up-large-table-sql-select-query/920/3 "2008-09-28T15:16:10Z")

</div>

Depending on how many Domains you have, you could try to split the data by domain id using merge tables…

For each domain another table… merged to one big mergetable for “global” overall access… if needed.

You cold also normalize usernames (limit special chars, store in uppercase…) and use binary collation for comparison…

---

<div class="post-metadata">

**Author:** ![MarkRose](https://avatars.discourse-cdn.com/v4/letter/m/85f322/32.png) [@MarkRose](https://forums.percona.com/u/MarkRose)\
**Post date:** [September 28, 2008, 6:59pm UTC](https://forums.percona.com/t/speed-up-large-table-sql-select-query/920/4 "2008-09-28T18:59:59Z")

</div>

That scares me, because I’m writing a social media application that will have a practically identical query.