Question: 10 Million rows taking 3.50 seconds for a simple type query

Question

10 Million rows taking 3.50 seconds for a simple type query

Answers 1
Added at 2017-01-01 15:01
Tags
Question

I'm new to database query optimization. Here is the create table query:

CREATE TABLE mo (
  id int UNSIGNED NOT NULL auto_increment,
  msisdn varchar(20) NOT NULL,
  operatorid int UNSIGNED NOT NULL,
  shortcodeid int UNSIGNED NOT NULL,
  text varchar(100) NOT NULL,
  auth_token varchar(60) NOT NULL,
  created_at DATETIME,
  PRIMARY KEY(id)
);

My query is this:

SELECT count(id) as mo_count from mo where created_at > DATE_SUB(NOW(), INTERVAL 15 MINUTE)

When I tested it the result was

Time taken for tests:   3.50 seconds

[0] => Array
(
    [id] => 1
    [select_type] => SIMPLE
    [table] => mo
    [type] => ALL
    [possible_keys] => 
    [key] => 
    [key_len] => 
    [ref] => 
    [rows] => 10000255
    [Extra] => Using where
)

Please teach me how to optimize this query. Thank you so much.

Answers
nr: #1 dodano: 2017-01-01 15:01

You will need to add an INDEX on the column you use for that query, otherwise the database will have to check all of the rows in your table to see which fits your WHERE clause.

ALTER TABLE mo ADD INDEX (created_at);

The index gives MySQL the ability to scan only part of your table, based on the data you have in that column.

You can read more about how mysql uses indexes.

Regarding the query itself - without changing the structure of your table you can't really optimize it (unless you know which ids fit the WHERE and you can change the query to use the id> X, because the id column in your table is indexed, but I guess it's not really the case).

Source Show
◀ Wstecz