SQL Simple Query Modification

1. Group BY order bySelectSiteID Count (*) as 1   fromWebDBwhereGathtrime between'2017-06-14 00:00:00'and'2017-06-14 23:59:59'GROUP BY SiteID ORDER by 1desc2 inchLimitSelect* fromSitemanagewhereUrlinch(SelectUrl fromCurl) limit0, +3TimeSelect* fromSitemanagewhereSiteid= Aand gathertime>'2017-06-12 00:00:00 ' ORDER by gathertime Desc4Update SitemanageSetupdatestrategy=3 whereIdinch(SelectSiteID fromWebdewherenewurl!='(NULL)'ORDER by SiteID ASC) 5update sitemanageSet=replace (URL,"',"')6. SELECT ID, URL, configs from Sitemanage WHERE (INSTR (Configs,'div.title>') >0) SELECT ID, url, configs from Sitemanage WHERE (INSTR (configs,'<filterreg>')=0)7. SELECT * from Sitemanage WHERE (INSTR (Configs,'H1') >0) ORDER by URL ASC LIMIT -,32838Update SitemanageSetSitemanage.vname = (SelectVName fromContrastwhereSitemanage.id=contrast.id) URL contains chinese characters 9SELECT URL from sitemanage WHERE length (URL)!=char_length (URL)TenSELECT ID from Siteid2 WHERE ID notinch(SELECT ID from metasearchconf_id WHERE ID between94366and97000) and (ID between94366and97000) LIMIT to OneUpdate SitemanageSetupdatestrategy=2, configs= replace (configs,'<titlefilter><! [cdata[td.title]]></titlefilter>','<titlefilter><! [cdata[table.title>tr>td[align=center]]]></titlefilter>') WHERE URL like'http://www.ln.gov.cn%'#SELECT* fromSitemanagewhereIdinch(SELECT ID fromcontrast) #update sitemanageSetupdatestrategy=3 whereIdinch(SELECT ID fromcontrast) #SelectCOUNT (ID) fromSitemanage WHERE updatestrategy=3and (IDinch(SELECT ID fromcontrast))
Query section:
1. Equivalent connection (from A, b) > Outer connection (from a LEFT join B.) > subquery.
2, according to the principle of small results drive large result set to write the order of where conditions.
3, do not use not in, can not be replaced by exists.
4, minimize the string, the time Type field with >, &lt, and can be replaced by between and. The function is not available in the Condition column for example: Year (publishtime) >2016.
5. When the amount of data in multi-table query is large, use the association instead of in, do not use or connect.
6, develop the use of explain detection statement efficiency
7, Multi-table query the best way to optimize is to build a view, improve the logical independence of the table, but also to shield the original table structure changes caused by the impact.


Index section:
1, a query can only use an index, that is, where conditions and indexes to use the necessity.
2, choose to establish a single-column index or composite index, the composite index is also in accordance with the left-to-right order, and be sure to use the most frequently used fields on the left. If the first is not matched to a condition, the index of the other field is not used.
3. When you associate a query, the field types are the same on both sides of the condition and the indexes are built to speed up.
4. The indexed field default value is NOT NULL
5, like '%xxx% ' is not used in the index, but like ' xxxx% ' will be used.
6, try not to index large text or long string, you can build a prefix index.
7. Tables that often have deletions, or tables with large amounts of long text, are periodically defragmented. Using the MyISAM engine, for example, when we delete a table, the * in data. The size of the MyD file is not smaller and will affect our storage and query. So make regular cleanup (OPTIMIZE table ' * * * * can be multiple tables ', ' Connect ')
8\\ a time period query statement in a table
SELECT COUNT (URL) from Wise_web_docinfo WHERE gathertime between ' 2016-05-30 00:00:00 ' and ' 2016-05-30 23:00:00 '
9\\url Fuzzy Query
SELECT * from Sitemanage WHERE URLs like '%0634% '
10\\ change other fields in the table based on the URL field
UPDATE sitemanage SET category= ' Forum d ', updatestrategy=2 WHERE url like '%0634% '
11\\in condition, limit condition query
SELECT * from WebDB WHERE url in (select URL from curl) LIMIT 0,1000
12\\ Time Query
Select?*? From?wise_web_docinfo? WHERE?? site_id?=-7651? And?gathertime?> ' 2016-07-12?00:00:00 '? ORDER? By?gathertime? DESC
13\\in
SELECT * from Sitemanage where ID in (SELECT siteid from waitlist where newurl! = ' (NULL) ' ORDER by SiteID ASC)
14
UPDATE sitemanage SET updatestrategy=3 where ID in (SELECT siteid from waitlist where newurl! = ' (NULL) ' ORDER by Sitei D ASC)
15
SELECT * from ' thai_com ' ORDER by gathertime LIMIT 0, 10;
16
Update ' Baidutieba ' set url= replace (URL, ' http://tieba.baidu.com//f?kw ', ' http://tieba.baidu.com/f?kw ') 

SQL Simple Query Modification