Tag Info

Hot answers tagged

4

You should try partitioning: TABLE PARTITIONING Table partitioning is a good solution to this very problem. You take one massive table and split it into many smaller tables - these smaller tables are called partitions or child tables. Operations such as backups, SELECTs, and DELETEs can be performed against individual partitions or against all of the ...


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 ...


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 ...


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 ...


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


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 ...


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 ...


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() ...



Only top voted, non community-wiki answers of a minimum length are eligible