1

I have a small table with three columns and using PHP.

TABLE (id unsigned int(10), gameid unsigned int(10), userid unsigned int(10)) - id, gameid and userid are indexed.

At some point (When a player join a game) system will check if the game already have 100 players in it, if so it retrive all rows and do some actions over them and in the end that rows will get deleted.

SELECT Query: 

SELECT * FROM table WHERE gameid = 1 ORDER BY id ASC LIMIT 100

COUNT Query:

SELECT COUNT(*) as rows FROM table WHERE gameid = 1 LIMIT 100

DELETE Query: 

DELETE FROM table WHERE gameid = 1 ORDER BY id ASC LIMIT 100

My question is about performance and speed, what will be better:

Approach 1 (3 Queries)

  • Execute COUNT query and check how many rows are in table
  • If rows >= 100 do:
    • Execute SELECT Query and process the data
    • Execute DELETE Query

Approach 2 (2 Query)

  • Execute SELECT Query
  • If count(rows) >= 100 do:
    • process the data
    • Execute DELETE Query

What approach will be better to use?

Thanks

Tiago Conceição
  • 391
  • 4
  • 18

1 Answers1

2

microtime(true) is your PHP friend.

If there is an index starting with gameid, "Approach 1" might be faster, since it can do the COUNT(*) in the index.

On the other hand, there are two things that count against it:

  • 3 queries is usually slower than 2.
  • If there are a lot more than 100 rows with gameid = 1 (which does not seem to be the case), it would spend more time doing the full count. "Approach 2" stops at 100.

I think the real answer is "there is not enough difference between the two to matter.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Yes i done a little test and count itself is slower than the select query, not make much sense but the difference is big. Count query takes 0.00xxx to execute and select takes 0.000xxx to execute, so count+select will take 3times more than select only – Tiago Conceição Jun 20 '15 at 03:35
  • Probably a false alarm -- Doing the `COUNT(*)` may be bringing various blocks into cache (RAM). Then the other `SELECT` does not need to load those blocks. Or, you have the Query Cache turned on and you had just done that `SELECT` recently. – Rick James Jun 20 '15 at 05:25