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,
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
 => Array
[id] => 1
[select_type] => SIMPLE
[table] => mo
[type] => ALL
[rows] => 10000255
[Extra] => Using where
Please teach me how to optimize this query. Thank you so much.
10 Million rows taking 3.50 seconds for a simple type query
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.
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).