There is a table (mysql) with a field binary . With the growth of the database (1Gb), SQL queries with count started to take a long time to execute. How to solve this problem?

UPD:

This table has a binary field.

 select count(*) cou from docs where status_id=1 and language_id=1 and user_id=10
  • If only a sample code is shown for a start! - Palmervan
  • show the request) - timka_s
  • 2
    Indexes. - alexlz

3 answers 3

binary field may well affect the query

 count(*)

counts the total number of all fields, but if you make

  count(id)

then the request will be easier! check!

on a piece, but there must be less time.

  • one
    count (*) is a muscle-optimized construction, and it will be faster than count (id) - draev
  • but count (id) will not select a record with NULL which can be many, and if the condition is to get a clear value of the number of rows, then count (id) is Artem
  • did not help :) - riskk
  • id in the index? - Artem
  • count ( ) - counts the total number of lines. count (id) - counts the number of lines for which id is not null count (id) - requires accessing raw data (if there is no index on id) count ( ) - does not require accessing raw (but conditions may require) - timka_s

Make a single index for 3 fields ( status_id,language_id,user_id ) - and the count (*) will be free

    User_ID I would have indexed. Apparently, it has the widest range of values ​​among the three specified attributes.

    • The problem is that there are not so many records in the table itself, the whole problem is that the table is inflated due to the binary field. I did the indexing on the required fields, it did not help much. - riskk
    • In general, IMHO, it should not affect the query :-(. And the binary field can not be removed in another table? In the child? Try it without it. - BuilderC