Tag Info

New answers tagged

1

Learn about composite indexes; use them where appropriate. For example: ON cpr.product_id = cp.id AND cpr.branch_id = 3 begs for either of these: INDEX(product_id, branch_id) INDEX(branch_id, product_id) Indexing flags (such as active) rarely useful. Don't use LEFT unless the "right" table has optional data. What do you expect the ORDER BY to do if ...


1

it's better to use partitioning, e.g. one table per month. You can then truncate archive tables which is an instant operation which frees disk space, or move them to a tablespace on a cheaper device and/or replace them with an aggregate row in an aggregates table. The table seen by your app will usually be a view of the union of the monthly tables. You need ...


0

MySQL still (I thought they were fixing this) requires that the left most part of an index is used in the query: http://dev.mysql.com/doc/refman/5.6/en/multiple-column-indexes.html Yes, you want the highest cardinality first always. Be aware that like oracle, innodb stores its indexes in clustered leafs off the primary index, so any secondary index call is ...


0

If you are going to make a new entry means, You can just do Insert into tablename select query If you want to update your table based on some values, but the values are need to took from some other table through joining(Select query which you mentioned)you can store those values in temp table.And then you can iterate those values through do-while or ...


1

You can indeed use stored procedures to refresh the data in your database. It just means coding the INSERTs and UPDATEs appropriate to your data. If you are replacing the existing data with new data, you could use: INSERT INTO ... SELECT FROM ... syntax, after deleting the existing data, to pull the data from your query and insert it into the now empty ...


2

Well, you can check how often your plans are reused. After that, you may want to decide to switch to optimize for ad-hoc workloads. Those flag will only save a plan-stub instead of a full plan and will do a fast compile of the stub if there is a query which uses the same stub. Those option isn't enabled by default (due to the fact that it's a newer ...


-1

cc needs INDEX(pap, stati) (or in the opposite order). Please provide SHOW CREATE TABLE.


2

You can use a regular expression to match '1 ' to '6 ' at the beginning of the string: data.pro rlike '^([1-6] )' Now put this in a CASE: case when data.pro rlike '^([1-6] )' then cast(substring(data.pro from 1 for 1) as unsigned) end


0

If you have an index on data.pro: select substring(min(data.pro),1,1) from products where substring(data.pro,1,2) in ('1 ','2 ','3 ','4 ','5 ','6 ') order by data.pro; Instead of a select case on each record of your products, take the minimum-record between for your range from 1-6 (starts with that number anyway). If there is a good index for that, only 1 ...


1

It depends. INDEX(a) SELECT ... ORDER BY a LIMIT 10 -- will, if there is no `WHERE`, stop after 10 rows are read INDEX(b,a) SELECT ... WHERE b=3 ORDER BY a LIMIT 10 -- is likely to use the index for both the WHERE and the ORDER BY, and stop after 10 INDEX(b,a) SELECT ... WHERE b=3 AND c=4 ORDER BY a LIMIT 10 -- This _may_ use the index, but _may_ have to ...


2

I have found out the solution by looking other similar questions, this query runs fast. Hopefully it will help someone that is having similar issue. SELECT vendor_products.* FROM vendor_products LEFT JOIN products a ON a.gtin = vendor_products.gtin LEFT JOIN products b ON b.gtin = Concat(0, vendor_products.gtin) LEFT JOIN products ...


1

Even in your updated query, you hit FIELDDATA eight times and Events three times. Rather than focusing so much on indexes, let's refactor to reduce the number of table hits. See below for an example. This is just my best guess at a refactor without a full understanding of your data, so keep or ignore the bits that work for your data. Changes include: Use ...


1

PARTITION is unlikely to help. CHAR(32) -- is it utf8? If so, then it is taking 96 bytes; what a waste. And it is hex, correct? Consider converting to binary and using BINARY(16) to save space. Note that (in InnoDB) the PRIMARY KEY is included in each secondary key. So, 8 indexes times (96-16) bytes = 640 bytes per row saved. In MySQL there are HEX() ...


3

By suggestion of @RolandoMySQLDBA, I'm combining my comments into an answer. So, in many cases, LIMIT clause enables the database server to do less work before delivering the result. MySQL docs explain such situations. It is best to check queries with EXPLAIN to see the execution plan. If the execution plan is different with and without limit clause, then ...



Top 50 recent answers are included