Databases
StackOverflow2013
Postsdbo
POMysql with big tables: how to optmize this query?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMysql with big tables: how to optmize this query?
    primarykey
    data
    text
    <p>I have a table using InnoDB that stores all messages sent by my system. Currently the table have 40 million rows and grows 3/4 million per month.</p> <p>My query is basically to select messages sent from an user and within a data range. Here is a simplistic create table:</p> <pre>CREATE TABLE `log` ( `id` int(10) NOT NULL DEFAULT '0', `type` varchar(10) NOT NULL DEFAULT '', `timeLogged` int(11) NOT NULL DEFAULT '0', `orig` varchar(128) NOT NULL DEFAULT '', `rcpt` varchar(128) NOT NULL DEFAULT '', `user` int(10) DEFAULT NULL, PRIMARY KEY (`id`), KEY `timeLogged` (`timeLogged`), KEY `user` (`user`), KEY `user_timeLogged` (`user`,`timeLogged`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;</pre> <p>Note: I have individual indexes too because of other queries.</p> <p>Query looks like this:</p> <pre>SELECT COUNT(*) FROM log WHERE timeLogged BETWEEN 1282878000 AND 1382878000 AND user = 20</pre> <p>The issue is that this query takes from 2 minutes to 10 minutes, depending of user and server load which is too much time to wait for a page to load. I have mysql cache enabled and cache in application, but the problem is that when user search for new ranges, it won't hit cache.</p> <p>My question are:</p> <ul> <li>Would changing user_timeLogged index make any difference?</li> <li>Is this a problem with MySQL and big databases? I mean, does Oracle or other DBs also suffer from this problem?</li> </ul> <p>AFAIK, my indexes are correctly created and this query shouldn't take so long.</p> <p>Thanks for anyone who help!</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USjoe.dawley
    1. USFernando
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMysql with big tables: how to optmize this query?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMysql with big tables: how to optmize this query?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COPost the output from the following `EXPLAIN SELECT COUNT(*) FROM log WHERE timeLogged BETWEEN 1282878000 AND 1382878000 AND user = 20;`
      singulars
      1. POMysql with big tables: how to optmize this query?
      1. USJason McCreary
    2. COI'll post this as a comment b/c it doesn't address query-optimization, but have you considered an archiving strategy instead of keeping all messages in a single table? 40m records at a rate of 750k/month implies over four years' worth of data. Unless it is truly the case that messages at any arbitrary age are queried with equal frequency, you may want to consider moving old messages to a separate table, and implementing logic that directs requests for old messages to that table.
      singulars
      1. POMysql with big tables: how to optmize this query?
      1. USDan J
    3. COThere isn't much to optimize in the query. What size is your *key_buffer*?
      singulars
      1. POMysql with big tables: how to optmize this query?
      1. USAndreKR
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload