New answers tagged

0

Short answer: Don't worry. Long answer: If you are willing to limit to 64KB, use TEXT instead of MEDIUMTEXT. I don't think VARCHAR(5000) has any benefit over either of them in any version of MySQL. Is the question about MEDIUMtext? MEDIUMTEXT has a 3-byte length field for overhead; TEXT and VARCHAR(256 to 65525) has 2-byte. The difference is ...


0

varchar columns can handle 65535 characters. All varchar, char, *text and blob above after 768 bytes these are stored off-row. So you still can use varchar of a higher value, or text types however do you see fit. At some point older MySQL versions couldn't sort in memory on a text column but I think that was fixed in 8.0. Don't worry about the performance or ...


3

There are really three different questions in here. Q: Is a table with hundreds of thousands of rows huge? Generally, no. For example, you can download the Stack Overflow public data export, and it has tables with over a hundred million of rows. The definition of a very large database (VLDB) has changed over time, but you'll often hear folks complaining ...


0

Can you afford the time to create/maintain an INT OR BIGINT column that orders each users rows in the table? This includes reordering all rows for a given user for late arriving data. Then the pagination becomes super simple math. For example, set up with this: ALTER TABLE testing_performance ADD user_ordinal BIGINT; CREATE UNIQUE INDEX ...


2

Without more information on your use cases and the type of data, it's hard to say what's the right fit. Regarding performance, when architected properly, a relational database management system (RDBMS) will perform no differently than a NoSQL system, no matter how much data you plan to store. Aside from that, some people do have the use cases for leveraging ...


4

First off, to answer your question: Yes, it can affect performance, as space needs to be allocated to hold large values in the query engine. In your case, you could also use a suitably large size such as varchar(50) which would easily hold whatever you needed. But you shouldn't be doing any of this in the first place. When rounding dates, you should not ...


2

Overview of Optimizer Hints ... A statement block can have only one comment containing hints, ... select /*+gather_plan_statistics no_index(t idx1_dimcustomer) */ * from mi_dimcustomer t where t.customer_num = 12;


0

Now that we have the real requirement (return a page of results for the user) we can suggest ways to make that achievable. Your current method of pagination is visiting all the rows in the table that match your filters, then sorting them, then returning a range of 50 depending on what page the user is on. This is very possible to do fast. You would have an ...


0

I notice that this question was posted nine years ago, so the figures in my answer (which are current as of 2021) won't be relevant to that time. But I suspect your home machine does not have enough RAM to host your own server. I had that problem, locally hosting MSSQL server 2012 with 8GB of RAM on a Windows 10 machine, with Core i7 10th Gen. Sometimes ...


1

The default installation is fine for typical usage. If you have extremely heavy usage, then explain what kind of app you have and how much RAM and data. Swapping is bad for performance, but not having swap space when needed is worse. Still, generally a dedicated server with a default configuration is not likely to hit such. A "busy system" would ...


1

Your data are not cached, and your index seems bloated. Besides, you didn't show the complete execution plan. Besides, an index to filter 1.8 million from 8 million will speed up things, but probably not very much. You should VACUUM the materialized view and set work_mem high. It seems like most of the time is spent reading the many blocks from disk. If you ...


0

There may be a substantial impact on the first few queries after the rows were deleted, because autovacuum hasn't finished processing the table yet and queries need to dig through a lot of index entries that belong to deleted rows. However, these index scans will mark the index entries as "dead", and subsequent index scans will ignore them. After ...


-2

in temp table you can make some index field , for example other table`s name , and make a regular index with integer like id . its best way to speedup your queries .


1

Do you have something like a "data warehouse"? And you are doing "reports? That is, if you are summarizing (COUNT, SUM, etc) the information, then build and maintain "summary table(s)". More discussion: http://mysql.rjweb.org/doc.php/summarytables That will be much faster and can be as up-to-date as necessary. Please provide ...


0

I was able to reduce my SELECT query against INFORMATION_SCHEMA from 12s to 0.001s by adding AND TABLE_SCHEMA='DB_NAME' to the query, on MySQL 5.6. Before (12s): SELECT AVG_ROW_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'myTableName'; After (0.001s): SELECT AVG_ROW_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '...


1

Create indexes on temporary tables. Syntax is same as for regular tables.


0

Some of the columns in the Availability Group Dashboard are calculated, such as the Estimated Recovery Time (seconds) which you can replicate by dividing the redo_queue_size by the redo_rate in the sys.dm_hadr_database_replica_states DMV.


0

A FK provides 2 things: An integrity check. This means that an INSERT will verify that the corresponding row in some other table already exists. This takes a small amount of time An INDEX. An index is automatically added if there is not one already defined. You probably need this index for other queries, so the performance hit for this is effectively ...


0

Where practical, measure the time taken for each subquery. Then focus on the slowest subquery. When a SELECT involve JOINs and a LIMIT (or a GROUP BY), an optimization is to turn it inside out. I am referring to the two derived tables with LIMIT 200. That is rewrite the subquery to find the ids of the 200 row as simply as possible, then JOIN to the other ...


Top 50 recent answers are included