Google Cloud SQL: Unable to execute statement

Question

My Google cloud sql table have 1126571 rows currently and adding minimum 30 thousand every day.When execute the query :

select count(distinct sno) as tot from visits

sql prompt it will generate following error:

Error 0: Unable to execute statement 

. Is Cloud SQL Query liable to 60 seconds exceed exception. How can overcome the problem when the table become large.

Madancy

Answer 1

Break the table into two tables. One to receive new visits ... transactions ... one for reporting. Index the reporting table. Transfer and clear data on a regular basis.

The transaction table will remain relatively small and thus it will be fast to count. The reporting table will be fast to count because of the index.

Renaerenaissance

Answer 2

add an INDEX in your column sno and it will improve its performance.

ALTER TABLE visits ADD INDEX (sno)

Avestan

in my table every seconds insertion is going on. So indexing will cause to reduce the insertion query performance – Madancy

@JinjuJoseph You may add a trigger on visits to update the value you desire in another table say totals in field tot. Then you would select the aggregated value directly from there. – Recess

Answer 3

Try to split your select query for many parts, for example, the first select query must be limited to 50000, and then the second select query must be started from 50000 and limited to 50000 and so on.

You can do that by this scenario :

1- Get records count.

2- Make a loop and make it end at the records count.

3- For each loop, make the select query select 50000 records and append the results to a datatable (depends on what's your programming language)

4- In the next loop, you must start selecting from where previous loop ended, for example, the second query must select the next 50000 records and so on.

You can specify your select starting index by this SQL query statement:

SELECT * FROM mytable somefield LIMIT 50000 OFFSET 0;

Then you will get the whole data that you want.

NOTE : make a test to see what's the maximum records count can be loaded in 60 sec, this will decrease your loops and therefore, increased performance.

Hart