Hot answers tagged

9

as it turned out, it wasn't the MIN()/MAX() functions that slowed the query down, but the WHERE DATE_FORMAT(strtime, '%Y-%m-%d') = CURDATE() statement. Since the table has a column with the unix-timestap, using this instead accelerates it a lot: WHERE TIMESTAMP > UNIX_TIMESTAMP(CURDATE()) Query-time from ~3.6s down to ~0.02s. I call that a good ...


4

The problem is here. WHERE DATE_FORMAT(strtime, '%Y-%m-%d') = CURDATE() strtime needs to be indexed, and the query needs to be written such that MySQL can use the index. Making a function call like date_format on it means MySQL may not be able to use the index. Instead, try date. Because date does a known conversion, MySQL may be able to use an index. where ...


3

There are three ways to handle DIVIDE BY ZERO error in your case : 1) USING IF() : ALTER TABLE report_user modify avg_trans_out_amount bigint GENERATED ALWAYS AS (round(trans_out_amount/if(trans_out_count,0,1))) STORED; 2) USING NULLIF() : ALTER TABLE report_user modify avg_trans_out_amount bigint GENERATED ALWAYS AS (round(trans_out_amount/nullif(...


2

UPDATE a SET col4 = 3 WHERE col1 = 1 AND col2 = 2 AND col3 = 3; Needs this composite index: INDEX(col1, col2, col3) -- in any order So, if you start it with col1, col2 (in either order), that index will also work nicely for UPDATE a SET col4 = 2 WHERE col1 = 1 AND col2 = 9999; If, by "We have indexes on all the columns" you mean single-column ...


2

I hope this is not the case, or you're going to get tons of problems down the line. It's a Bad Idea to have any kind of "special" character in column names and single quotes would be just mind-bendingly difficult to work with. Try this: select `'ID'` from table1 ; (That's Back-Tick, Single-Quote, capital-Eye, capital-Dee, Single-Quote, Back-Tick.)...


2

Could you use partitions? If your data is deleted by time like a time series rolling off, it is worth considering range partitions by such time (ex: by month). Then deletion would be a simple matter of dropping a partition. If instead a partition was based on a 'deleted' flag, you would truncate partition containing all rows with this deleted flag. But if ...


2

As you can see underscore is a wildcard in a LIKE comparison. If you want the underscore recognizes you need to escape it with \ ´ But this is basic sql and nothing strange. Finally it is good practise not to use wildcards reseved words in table names or column names, so you don't get confused and of course all others SELECT table_name FROM ...


2

You should use pt-online-schema-change. This will allow for no downtime and you can pause it in the middle of a production period Provided you have twice as much space free (at least 248GB) STEP 01 : Select Folder For this example, let's choose /home/whoeveriam/online_change mkdir -p /home/whoeveriam/online_change cd /home/whoeveriam/online_change STEP 02 : ...


1

When the field being sorted on is not unique, then the order of the results are non-deterministic, meaning there is no guarantee on their exact ordering. Specifically in your example case where the first 10 results all share the same exact value for the s field could yield indeterminate results for the first time you run the query vs the second time. It is ...


1

The WHERE clauses refer to t, so it is very likely that the Optimizer will start with t in each SELECT. You have the optimal indexes for them. Then it needs to reach into the other two tables (merchants and customers) and get 1 (or 0) row from them. Those tables have the optimal index for the JOIN, namely PRIMARY KEY(id) in each case. (The FKs do not play ...


1

Do you need all 20 columns from transaction_events? If not, then getting rid of * and specifying only the columns you need not only reduces the amount of data you're pulling back at one time but also reduces the chances of a sub-optimal query plan. It's possible the query plan generated will vary based on the columns in your SELECT clause. You could test ...


1

From MySQL documentation MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis. As long as ...


1

switch the datetime comparision so u dont apply funciton on column but on curdate. Ie. Strtime between A and B. primary key on energy_kwh is not very usefull here i think. And risky. What if there is a timespan with 0 energy consumption. No change in value, PK violation. add index on strtime,, move dattime calculation to other side and it should be ok.


1

How many Products do you consider a lot?...What do you define slow as? A correctly architected schema should be able to handle a Products table with 100s of billions of rows, even on modest hardware. (I've managed similar tables with 10s of billions of rows, that were quite performant, e.g. could query any specific product in 0 seconds.) Unless you plan to ...


1

You have an incorrect mix of operators in the wrong order, specifically your LEFT JOIN operator can't come after the WHERE or GROUP BY operators. (Normally what you're trying to achieve would be easy with CTEs but your version of MySQL doesn't have CTEs yet.) Re-writing the query with sub-queries like this is how you can achieve your goals: SELECT a.dt, a....


1

The transaction size is not just based on the amount of data you're actually change, rather the whole state of the row before you made the change is preserved in case of a rollback and therefore the entire row is essentially copied. From further research it sounds like as much as even the entire page of rows is copied to the rollback segment, so depending on ...


1

You can use simply HAVING in MySQL CREATE TABLE exam ( id int) CREATE TABLE dataset ( id int) select exam.id as exam_id, (select count(*) from dataset where dataset.id = exam.id) as n_dataset from exam HAVING n_dataset = 0 exam_id | n_dataset ------: | --------: db<>fiddle here


1

Because the order of operations with the WHERE clause is applied first before the SELECT operator. So n_dataset doesn't exist yet when the WHERE clause is being applied. You would need to use COUNT() as a window function (version of MySQL depending, you should update your tags to include the version) to get the results you're looking for. Or you can create ...


1

No database selected error message means that no database is selected as current. Specify default database in my.ini, connection definition, or execute USE dbname; as the very first query in the connection. why no other view required a default schema be defined, and why is a default schema required at all? – Roy Hinkley Default scheme needed when tablename ...


1

Column < Row < Block < Table One column had a number, date, string, or a small number of other possibilities. It takes a few bytes or many bytes. One row like you described (4 columns each taking a few bytes) will take perhaps 40 bytes when you add in some overhead. One block is 16KB and holds (in your case) a few hundred rows. I mention a block ...


1

Hi and welcome to the forum! Your schema requires a totally radical overhaul. This image gives some idea of what's wrong with it: You have used the EAV (Entity-Attribute-Value) database design anti-pattern - search for the writings of Joe Celko, Bill Karwin and others (and links therein) on this topic. Also, and very important, you should store VARCHAR()s ...


1

DDL changes implicitly commit only if they are in the same connection. As they are in a different connection from the mysqldump they lock wait until the dump is complete and then they will complete. As such hopefully DDL changes aren't as time critical and --single-transaction will suit your needs without any addition fiddling. Alternate would be to create a ...


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