Databases
StackOverflow2013
Postsdbo
POHow can I speed up my SQL query?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow can I speed up my SQL query?
    text
    copied!<p>Here is the query:</p> <pre><code>SELECT name, SUM( `count` ) AS Total FROM `identdb` WHERE MBRCONTAINS( GEOMFROMTEXT( 'LineString(34.4 -119.9, 34.5 -119.8)' ) , latlng ) AND MOD( DAYOFYEAR( CURDATE( ) ) - DAYOFYEAR( `date` ) +365, 365 ) &lt;=14 OR MOD( DAYOFYEAR( `date` ) - DAYOFYEAR( CURDATE( ) ) +365, 365 ) &lt;=14 AND MBRCONTAINS( GEOMFROMTEXT( 'LineString(34.4 -119.9, 34.5 -119.8)' ) , latlng ) GROUP BY `name` </code></pre> <p>It essentially finds any rows where the day of year is plus or minus 14 of today's day, and rows that the latlng spatial column is in the rectangle.</p> <p>Here is what my database looks like:</p> <pre><code># Column Type Collation 1 name varchar(66) utf8_general_ci 2 count tinyint(3) 3 date date 4 latlng geometry 5 lat1 varchar(15) latin1_swedish_ci 6 long1 varchar(15) latin1_swedish_ci Keyname Type Unique Packed Column Cardinality Collation Null Comment PRIMARY BTREE Yes No name 0 A count 0 A date 0 A lat1 0 A long1 6976936 A sp_index SPATIAL No No latlng (32) 0 A </code></pre> <p>There are 7 million records and the query is taking about 7 seconds. I have no clue how to speed this up, thanks in advance!</p> <p>EXPLAIN:</p> <pre><code>id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE identdb ALL sp_index NULL NULL NULL 6976936 Using where; Using temporary; Using filesort </code></pre> <p>UPDATED explanation of query: I believe MBRCONTAINS creates a rectangle where I can compare whether the latlng spatial point is inside or not. The date part is finding dayofyear + or - 14 days. It is using modular arithmetic so that it won't mess up around the new years. I had to put the MBRCONTAINS part in twice because of the use of OR.</p> <p>My needs of the query are to find find all <code>name</code>s that have a day of the year + or - 14 days, and are within the given lat/long pairs, and then total the counts for each.</p> <p>I'm dumb at this stuff so please correct me if I'm doing something dumb. Thanks guys!</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload