Mysql Select query speed up

Question

i am using to fetch record from database and records are likely be '590096' and query table time around 4 sec and i want to make it more faster which is like

less then 1 sec can you please guide me how should i do this i am using normal select query

SELECT count(*) from table where field6 > '$s'

i am also using date like below given code for condition

$t15m_ago = new DateTime("15 minutes ago");
$s = $t15m_ago->format("Y-m-d H:i:s");

would it be due to this?

Below is my database

CREATE TABLE table (
  field0 int NOT NULL auto_increment,
  field1 varchar(20),
  field2 int,
  field3 int,
  field4 varchar(255),
  field5 varchar(60),
  field6 DATETIME,
  PRIMARY KEY(field0)
)

What exactly i want to do with select query i count records from table will last 15 mins. my table is inserting thousands of records in every sec.

You should take a look at https://dev.mysql.com/doc/refman/5.0/en/select-optimization.html One thing you could try is using an index in your select instead of * — harris, Aug 21 '15 at 08:51

score 0 · Answer 1 · answered Aug 21 '15 at 08:52

0

You could create an index on field6. It will speed up queries with field6 in condition.

CREATE INDEX field6_idx ON table(field6);

answered Aug 21 '15 at 08:52

Peter Jurčo

31
1
2

here idx is already primary key which is unique so do we still need to create index inspite of that too? – Anil Kumar Aug 21 '15 at 10:00
Of course. You can have more indexes in one table. They have more functions than just uniqueness. Take a look [here](http://stackoverflow.com/a/708508/4724920) for more about different kinds of indexes. – Peter Jurčo Aug 21 '15 at 12:29

score 0 · Answer 2 · answered Aug 21 '15 at 08:58

0

To speed up the query you can do following things

- SELECT count(field0) from table where field6 > '$s' rather than using *
- Apply indexes to the column
- Add limit 1 as count(field0) will itself return 1 row

please let me know if you still need more information.

Thanks Amit

answered Aug 21 '15 at 08:58

Amit Shah

1,380
1
10
19

amit if adding limit 1 as count() will itself return 1 row then what is the use of that? here i do not know how much data it will be wether 1 2 or 100 or 1000 so their is no use to use limit here – Anil Kumar Aug 21 '15 at 09:54
@AnilDhiman Using `LIMIT 1` in a `COUNT` query makes no difference result wise. It will still count all the matching rows, but return only 1 row as a result (the number of matching rows), just like your original query. Whether using it actually affects performance, I couldn't say. – harris Aug 21 '15 at 10:58
@AnilDhiman we do have information_schema database exists for mysql server, which keeps track of meta information, like auto increment, total rows, last modified time of table etc. so when we use count() it will not actually calculate all rows from table, and it will get the counts from information schema database and related tables. – Amit Shah Aug 21 '15 at 12:47

Mysql Select query speed up

2 Answers2