MySQL,select uses in to query. There are about 5000 items in the collection after in. There are many records in this table. The fields in in query are indexed
. the original SQL is similar to this
SELECT a FROM table WHERE an in ("1x code 2") and ohterCol = "123"
how to optimize the efficiency of submitting queries
if the content in your in collection exists in this form instead of being queried by other statements
, then all I can think of is to arrange the contents in the collection in an orderly manner, and then increase the size of the innodb_buffer_pool. Can you improve the cache hit rate
5000 pieces of data through other queries?
if possible, it may be more efficient to join this query with the target table.
has always thought that index optimization is effective in where is useless in select but it seems that there is a performance difference between select nonclustered index columns, clustered index columns, unindexed columns and select *? ...
if the table table has a federated index (sex, id) and id itself is the primary key , does the following sql go to the index? select * from table where sex= m order by id limit 1000000,10 my feeling is that according to the execution order of sql ...
basic information: The datasheet has almost 10 million pieces of data, using the mycat sublibrary. the indexes in the data table are PRIMARY id AppName (AppName, custidStatus, channel) the table creation statement is as follows CREATE TABLE...