now connected with the union all 12 query the table , the efficiency of the data is very slow 10W SELECT COUNT (*) 7s time when they were used , are there any good query methods ,
------ Solution ----------- ---------------------------------
select count ( primary key )
----- - Solution --------------------------------------------
in Search field indexing
------ Solution ----------------------------------- ---------
indexing it, do not build large data query index is not court death . . .
I tested the next , count (primary key) the time actually increased
count (1) it ? Try
------ Solution -------------------------------------- ------
my next test , count (primary key) the time actually increased
COUNT (1) and COUNT (*) is the same, in which you can write any constants, such as COUNT ('CSDNDSB'), does not output a constant , not to find all the system table column names .
COUNT (1) and COUNT ( column name ) meaning , you can not put them together . The only comparable situation is the primary key column name is a single table's primary key column , ie non-empty single key clustered index , in this case to go unless you want to force the clustered index scan , or COUNT (1) better than COUNT ( column name ) because it allows plans to select the best estimate index , and no column output .
http://bbs.csdn.net/topics/390635419
------ Solution ----------------------- ---------------------
this should be regarded as relatively open, recommendation under discussion .
------ Solution ---------------------------------------- ----
ctrl + L facie query plan , you know where the bottlenecks are , and then targeted to optimize
------ Solution ------ --------------------------------------
design can not be changed ?
I mean the database structure
------ Solution ------------------------------- -------------
is a problem , you are the union 7 tables , try to let go of each subquery index should be faster , you can see the execution plan , the bottleneck should be in each sub- query , union all in itself What does not consume time
------ Solution ----------------------------------- ---------
changed after the first screening should hurry joint ? Another filter conditions are relatively fixed, when it comes to add an index on a column should also be a good way .
select * from (
select id from table1 WHERE xx = xx;
union all
select id from table2 WHERE xx = xx;
union all
select id from table3 WHERE xx = xx;
union all
select id from table4 WHERE xx = xx;
union all
select id from table5 WHERE xx = xx;
union all
select id from table6 WHERE xx = xx;
union all
select id from table7 WHERE xx = xx;
)
------ Solution ----------------------------------- ---------
select 1 from bean
------ Solution --------------------- -----------------------
select count (1) from bean
------ For reference only - --------------------------------------
my next test , count ( primary key) the time actually increased
------ For reference only ---------------------------- -----------
select * from (
select id from table1
union all
select id from table2
union all
select id from table3
union all
select id from table4
union all
select id from table5
union all
select id from table6
union all
select id from table7
) as temp WHERE xx = xx;
I do not query itself is incorrect ?
------ For reference only -------------------------------------- -
database table structure can not be changed , as this union all the tables are not already in lowering the efficiency of the ?
------ For reference only -------------------------------------- -
changed after the first screening should hurry joint ? Another filter conditions are relatively fixed, when it comes to add an index on a column should also be a good way .
select * from (
select id from table1 WHERE xx = xx;
union all
select id from table2 WHERE xx = xx;
union all
select id from table3 WHERE xx = xx;
union all
select id from table4 WHERE xx = xx;
union all
select id from table5 WHERE xx = xx;
union all
select id from table6 WHERE xx = xx;
union all
select id from table7 WHERE xx = xx;
) put all the conditions placed on each query , the efficiency has really improved a lot
------ For reference only ----- ----------------------------------
I want to know the landlord sql, no set of SELECT COUNT ( * ) how long it would take for ?
------ For reference only -------------------------------------- -
and learn some knowledge of mysql !
------ For reference only -------------------------------------- -
length to six characters
------ For reference only ----------------------------- ----------
No map no truth, and the implementation plan ------ For reference only ----------------------------- ----------
if it is not time consuming 10s
query to the data 6W bar connection 12 with a count query time is 8s
------ For reference only ---------- -----------------------------
count ( primary key field ) otherwise very slow query
- ---- For reference only ---------------------------------------
; implementation plan
------ For reference only ----- ----------------------------------
focus on large data query problem .
------ For reference only -------------------------------------- -
if it is not time consuming 10s
query to the data 6W bar connection 12 with a count query time is 8s
Your ultimate goal is to resolve the query is slow, slow , or count ?
Also see the execution plan your field names , looks like we have a relationship we engage in industry , ah,
------ For reference only ---------------------------------------
if it is not time consuming 10s
query to the data 6W bar connection 12 with a count query time is 8s
Your ultimate goal is to resolve the query is slow, slow , or count ?
Also see the execution plan your field names , looks like we have a relationship we engage in industry , ah,
are both slow, regardless of the query or , count their effectiveness are currently slow, sql optimization processed first , and then create an index , and now a variety of written, efficiency is No ! ------ For reference only -------------------------------------- -
irrigation only. . . .
------ For reference only -------------------------------------- -
not understand ...........
------ For reference only --------------------- ------------------
if it is not time consuming 10s
query to the data 6W bar connection 12 with a count query time is 8s
Your ultimate goal is to resolve the query is slow, slow , or count ?
Also see the execution plan your field names , looks like we have a relationship we engage in industry , ah,
are both slow, regardless of the query or , count their effectiveness are currently slow, sql optimization processed first , and then create an index , and now a variety of written, efficiency is No ! The problem is very clear , ah, you display the execution plan where you do not take the index query
union all no problem , the bottleneck is not in this , subqueries to go after the index this data should be no problem
------ For reference only ------------ ---------------------------
first 12 sql one an optimized , if you 12 sql execution time plus with the fastest over 10 seconds , then connected together so slow is also normal.
You can feel the sql sent to optimize space , the execution plan also made , let us help look .
------ For reference only -------------------------------------- -
changed after the first screening should hurry joint ? Another filter conditions are relatively fixed, when it comes to add an index on a column should also be a good way .
select * from (
select id from table1 WHERE xx = xx;
union all
select id from table2 WHERE xx = xx;
union all
select id from table3 WHERE xx = xx;
union all
select id from table4 WHERE xx = xx;
union all
select id from table5 WHERE xx = xx;
union all
select id from table6 WHERE xx = xx;
union all
select id from table7 WHERE xx = xx;
) put all the conditions placed on each query , the efficiency has really improved a lot
Count the same reason , the first 12 count, and then added , the speed should be much faster , right ?
------ For reference only -------------------------------------- -
mysql should have a system table records the number of rows , COUNT ( primary key ) is usually not necessarily efficient.
------ For reference only -------------------------------------- -
If the table's data does not change often , you can first check the data into a temporary table , subsequent read from the temporary table , so fast
------ For reference only - -------------------------------------
If I remember correctly , mysql where the record is the primary key index estimates the number of records is useful when optimizing the execution plan to provide a reference , he this table , it seems even the primary key index is not built , all is a full table scan
------ For reference only ---------------------------------- -----
If there is no where condition , count what columns are scanning operation
------ For reference only ------------------ ---------------------
mysql is recorded total number of rows of data , but it is not dynamic, such as I need ; after screening a condition number of lines , direct access is not accurate
------ For reference only ------------------ ---------------------
EXPLAIN select COUNT (1) from (
select userid from tradeinfo
where userid = 28
union all
select userid from travelinfo
where userid = 28
union all
select userid from projectinfo
where userid = 28
union all
select userid from lawinfo
where userid = 28
union all
select userid from loaninfo
where userid = 28
union all
select userid from educationinfo
where userid = 28
union all
select userid from fundinfo
where userid = 28
union all
select userid from creditcarinfo
where userid = 28
union all
select userid from cardinfo
where userid = 28
union all
select userid from tidymoneyinfo
where userid = 28
union all
select userid from financialinfo
where userid = 28
union all
select userid from bankinfo
where userid = 28
) as temp
execution efficiency is now 0.016s
affect the efficiency of the first screening in combination, do count (*) statistics , SELECT the fields behind the less the higher the efficiency , the retention of field conditions ,
------ For reference only ---------------------------------- -----
this really easy to get, in addition to accelerating select count the amount of data processed , the seemingly no direct way , or if you look at your business needs whether data must be accurate ? If you do not , then once daily or hourly aggregated store up, with direct access when
------ For reference only -------------------- -------------------
good yapping wow
------ For reference only ------------- --------------------------
do not know why this is the query
twelve tables full output ?
------ For reference only -------------------------------------- -
select count (1) from bean
------ For reference only ---------------------- -----------------
only sql query it ? The results are generally optimized little space, if only with pure sql query results and code can still ( application software ) used in combination
recommendations:
1, a single execution sub-queries, see how much each consuming
2, preferably in the application layer data aggregation
------ For reference only ----------------------- ----------------
------ For reference only ------------------------------ ---------
------ For reference only ------------------------------------- -
passing look at the landlord .
------ For reference only -------------------------------------- -
table too , the query is slower
------ For reference only -------------------------- -------------
------ For reference only ---------------------------------- -----
nothing more than to narrow your search
------ For reference only ------------------------- --------------
------ For reference only -------------------------------- -------
we had used a temporary table.
The 12 tables inside qualified data lead to a temporary table, and then on the temporary table to build several indexes.
but then larger than the data , 12 table data add up to more than 1000 million .
------ For reference only -------------------------------------- -
My advice is to give these 12 tables plus the trigger , there is an insert operation to meet the requirements of the data is automatically imported into a dedicated query table.
------ For reference only -------------------------------------- -
build my first Board index entries .
------ For reference only -------------------------------------- -
------ For reference only ---------------------------------- -----
this method is very good ! suitable for large amounts of data query search process Thank
------ For reference only --------------------- ------------------
this image would be nice , it is worth learning .
------ For reference only ------------------------------------ ---
milk this fascinating · · · ·
------ For reference only ---------------------------------------
me that Diao · actually, and I , like avatar · ·
------ For reference only ---------------------------------------
------ For reference only ---------------------------------------
indexed in the search field < br>
没有评论:
发表评论