Hi,
When you've more than 13 lakhs records in mysql table you'll definitely have a hard
time selecting set of rows in particular order. Even your table columns indexed,
query execution becomes slower and insert,update queries on those tables will be locked.
Consider following site_detail table which has 15 lakh records.
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| sid | int(11) | NO | PRI | NULL | |
| month | tinyint(3) | NO | | NULL | |
| year | int(4) | NO | | NULL | |
| server | varchar(100) | YES | MUL | NULL | |
| xpoweredby | varchar(100) | YES | MUL | NULL | |
| ip | varchar(20) | YES | MUL | NULL | |
| country | varchar(100) | YES | | NULL | |
| ns | varchar(500) | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
Have a look at the following query,
select sid,country,ns from site_detail where server='Apache' order by sid desc
when you execute this query, mysql will copies the data to temporary table and sorts it
then sends the data, untill it finishes insert and update queries on site_detail will be blocked.
SOLUTION:
There may be some better solution available but i'm posting here which worked for me.
Create another table with sid,server,count and update this table periodically (i.e. monthly), then
select the data from this table. thats it.
