2

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.

Code Diary
  • 417
  • 2
  • 7
  • 17
  • if this table continues growing, and you query the count often, you might be interested in a new design. For instance, you could keep the last records in an "always fresh" table which would be very small. Meanwhile, there would be a "history" table with all the records. You could also fiddle and partition your tables per certain periods of time (days?) so that would limit the IO for each count query. – Sebas Jan 01 '17 at 15:07

1 Answers1

7

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

Dekel
  • 60,707
  • 10
  • 101
  • 129
  • he asked how to optimize the query, not the model :-) – Sebas Jan 01 '17 at 15:01
  • I was kind of teasing. Your solution is the most obvious one ;) – Sebas Jan 01 '17 at 15:02
  • no no, you are correct! I really think it's important to explain this part as well :) you can check the update. – Dekel Jan 01 '17 at 15:03
  • Given the amount of records they have, I'd say it's not that bad but could stand some optimization, as discussed in this Q&A http://stackoverflow.com/q/1712994/1415724 found under "Related". So Dekel's answer pretty much answers the question. – Funk Forty Niner Jan 01 '17 at 15:05
  • Thank you so much @Dekel. Your explanation is very helpful. Happy new year by the way! :) – Code Diary Jan 01 '17 at 15:09
  • Wow congrats @Dekel! May I ask one more thing? What about if I'm going to insert rows to the same table, how can I make it handle several thousands of requests per second? – Code Diary Jan 01 '17 at 15:17
  • If you are talking about thousands of requests per second you would probably need a large server (enough cpu/memory) or you would like to switch to some no-sql solution, which might be better for you. – Dekel Jan 01 '17 at 15:19
  • The rows that will be inserted to the table will come from an api request. I'd like to make sure that the program will be able to handle thousands of requests per second. Since you mentioned NoSQL, I think sql optimization will not be enough for that to happen and I should use caching or use nosql database. Is there anything you can recommend? – Code Diary Jan 01 '17 at 15:25
  • I'll check that. Thank you again @Dekel! :) – Code Diary Jan 01 '17 at 15:36