-1

I have a script that I need to run in under 1 second. I have this line that takes 4.4 seconds on average to execute.

    $result = mysqli_query($conn, "SELECT count(*) from record where created_at > '$time'");

The rest of the script takes less than 0.1 second.

Phillis Peters
  • 2,232
  • 3
  • 19
  • 40
  • 3
    Do you have any index on this table? – Dominique Vienne Jun 12 '17 at 15:25
  • When selecting all your really can't without putting an index on your table. You could select only the columns you need ? (unless you literally need all of them). Also look into using prepared statements because this isn't secure. – clearshot66 Jun 12 '17 at 15:26
  • 1
    I wonder if `count(1)` would be faster than `count(*)`. nevermind https://stackoverflow.com/questions/1221559/count-vs-count1 no difference – xQbert Jun 12 '17 at 15:27
  • it could also depend how you're fetching/looping. There isn't enough information on the schema/columns/values and how this is used. – Funk Forty Niner Jun 12 '17 at 15:32
  • 1
    *"Also look into using prepared statements because this isn't secure"* - @clearshot66 - Only if there is user input, and that we don't know and as to the rest of what wasn't posted. – Funk Forty Niner Jun 12 '17 at 15:35
  • 2
    Run `EXPLAIN SELECT count(*) from record where created_at > NOW()` and check if it's hitting any performance bottlenecks (e.g. no index on created date etc). – apokryfos Jun 12 '17 at 15:40
  • @DominiqueVienne When I run `SHOW INDEX FROM record\G` I see that the primary key, which is an auto-incrementing integer, is the index. – Phillis Peters Jun 13 '17 at 07:35

1 Answers1

0

I aggree with @xQbert and wonder if COUNT(1) or COUNT(ID) could increase speed... but nevermind...

Edit - just had the answer Count(*) vs Count(1)

You should add an index involving created_at row. Try to add a UNIQUE INDEX on created_at if possible or on created_at and another row (i.e. created_at + user_id). The more restrictive you will be, the faster you will get your results.

Another point is, if you only need 5 results... limit your query with LIMIT 0,5

Believe me... PHP won't be faster than MySQL! ;)

Dominique Vienne
  • 405
  • 2
  • 9