Tag Info

New answers tagged

1

You have 2 temporary tables being created. It is highly likely that the big difference comes from the fact that MEMORY, the engine used by MySQL to create temporary tables in, well, memory does not support TEXT/BLOB data types (independently of max_heap_table_size), so they are forced to be created on disk, having a great slow down . Profiling your query as ...


0

Adding the ORDER BY clause made it run almost instantaneously and I realized that it was only returning records from the first date because, duh, it was a SELECT TOP 10 query, there were more than 10 records from the first date. If I turned it into a SELECT * query I got everything in the range and if I added the ORDER BY clause I got it quickly. Thank ...


8

This is a frequent problem with poorly chosen clustered keys. Time series in general should be organized by time, since most queries ask for time ranges. Case in point, your query. If you have a correlation between id and timestamp then you can add an appropriate id based predicate: SELECT TOP 10 * FROM messages m INNER JOIN ...


2

This was rather longshot but since the OP says it worked, I'm adding it as an answer (feel free to correct it if you find anything wrong). Try to break the internal query into three parts (INNER JOIN, LEFT JOIN with WHERE IS NULL check, RIGHT JOIN with IS NULL check) and then UNION ALL the three parts. This has the following advantages: The optimizer has ...


0

Depending on the meaning of where not(pe.ThisThing = 1 and se.OtherThing = 0) and the id format you have chosen to use I would try different indexes and something like this: /* http://dba.stackexchange.com/questions/71415/optimize-select-on-subquery-with-coalesce */ /* assuming: where not(pe.ThisThing = 1 and se.OtherThing = 0); or the Id format of ...


0

Usually FULLTEXT searches need more than a single letter, there is actually a setting that prevents them from being used if less than X number of characters are used. ft_min_word_length: The minimum length of the word to be included in a FULLTEXT index. Note: FULLTEXT indexes must be rebuilt after changing this variable My guess is that you are not ...


0

My intuition would be that this should not be a problem as by the time COALESCE(pe.StaffName, se.StaffName) AS StaffName does anything all the rows from the two sources should have already been pulled in and matched up so the function call is a simple in-memory compare-to-null-and-pick. Obviously this isn't the case so perhaps something in one of the sources ...


5

I wrote a series on SQLServerCentral about baselines that might be of interest to you: http://www.sqlservercentral.com/Authors/Articles/Erin_Stellato/351331/ And as Shawn so kindly mentioned, I also have a Pluralsight course. If you have more questions, feel free to contact me (erin at sqlskills dot com). Erin


2

Troubleshooting Performance It is all about the queries. You need only three bits of information about your queries: CPU, Duration & Reads. SELECT TOP 50 qs.creation_time , qs.execution_count , qs.total_worker_time as cpu , qs.total_elapsed_time as duration , qs.total_logical_reads as reads , t.[text] FROM sys.dm_exec_query_stats qs CROSS APPLY ...


1

See this article about the performance problems and possible solutions using ORDER BY ... LIMIT. I would create an index on ord.ID DESC and remove the subquery. This is assuming that ID is not a primary key and indexed already. SELECT ord.ID, op.name AS prodName, op.code, ord.date, ord.email, op.total, stat.ID AS statusID, stat.value AS status, ...


4

Forget about mysqltuner and check for human advice. This tool tells you general recommendations that may be useless and even hurtful in some cases. Optimize table is probably going to be useless, but it locks your tables for writes. A consultant may save you time and money in the long run. Swapping should be a no-go for MySQL. Make sure to tune your ...


0

Refer to Johan's answer here, particularly the bit about covering indexes in InnoDB. My understanding of what he has written is that you should change your table definition to the following CREATE TABLE `t1` ( `pinc_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `pinc_codigo` char(10) COLLATE latin1_general_ci NOT NULL, PRIMARY KEY (`pinc_id`) ) ...


2

Assuming you have 300 Bytes per row, that makes a whopping 95GB per three weeks - that's not very much in today's terms - a 1TB disk would last 30 weeks - that's almost 1/2 of a year. If you compressed this data, I'm fairly sure that you could store at least a couple of years (possibly a lot more) on a single 1TB disk. I would keep the "live" data on one ...


0

First of all, are you creating your tables with CREATE TABLE table_name TABLESPACE hhmefep_tbs STORAGE DISK ENGINE NDBCLUSTER; or just CREATE TABLE table_name ENGINE=NDBCLUSTER; First one is to create table to stores table data on disk, the second, which is default mysql cluster theme and the reason why mysql cluster exist, stores data in memory. So ...


2

Back in the day we would bulk load our data in this way: Drop indexes Load data in the order for which the clustered index would be built (i.e., you export the data in a precise way) After the load is completed, create the clustered index Next, create any additional non-clustered indexes Miller time (this was before I could afford decent beer) That ...


1

If you are doing a large, load operation it is faster to utilize the TokuMX bulk loader, as it only requires one pass over the data to create both the primary key index and any secondary indexes. More information is available in the documentation at http://docs.tokutek.com/tokumx/tokumx-commands.html#tokumx-new-commands-loader


2

bytea will be optimal for storing the hash. It'll be transferred in/out of the database as a hex string anyway, unless you use PostgreSQL's binary wire protocol (supported by libpq and partly by PgJDBC) to transfer them. For best results, store as bytea and have the client application use a PQexecParams call that requests binary results. Though on ...



Top 50 recent answers are included