Tag Info

New answers tagged

0

Use a common table expression (CTE) for the inner query so it is not calculated over and over again. Take a look at CTE documentation: enter link description here


0

I removed the quotes so you'll have to re-add them. I think this should do the trick though. SELECT l.TrainUID, l.RouteUID, l.LegUID, l.StartTime, l.EndTime, l.TravelTime, l.DwellTime, s1.StationName, s2.StationName FROM LEGS AS l INNER JOIN ROUTES as r ON (r.RouteUID = l.RouteUID) INNER ...


1

You're correct: FULLTEXT search didn't hit InnoDB until MySQL 5.6. This leaves you with a few options: Update to MySQL 5.6 and use a FULLTEXT index Change the contract of your function to only allow prefix searches; that is, 'term%'. It will fulfill many use cases while saving your DB. Convert to a MyISAM table, or create a spare MyISAM table that you can ...


0

It is not a great practice to mix the app and database on the same server. You're competing for resources. You also mentioned remote execution versus local execution on the same disk. I'd contend that you could suffer from disk contention if it was all on the same disk and the overhead of the remote replication is questionable by comparison. A lot of factors ...


0

Many things can be done to speed things up... Use FLOAT or DECIMAL, not VARCHAR for latitude and longitude. (This is one of many things to shrink the record size.) Do not INDEX boolean values like clocked_in and public_post. (The optimizer is unlikely to ever use the index. And it is costly to update.) Don't split DATE and TIME; use DATETIME instead of ...


0

Your best approach is to rethink your design. The better way I can think of is to have one table that holds campaign level info (the name of the campaign, when it was created, by whom, etc). Then another table that holds all your recipient data (name, email address, etc). Finally another table that lists out each actual email (this is what would hold the ...


1

It depends a little on what storage system you have behind the scenes. You see, read and write IO operations are very different. On a RAID 5 to perform a single block write you must: Read the update block. Read the parity block. Write the new block. Write the new parity block. So for a single random write, RAID 5 needs 4 operations per write. This is ...


5

There are two types of activity to consider here: reads and writes. As you correctly point out, MDF/NDF writes are done through the checkpoint process, and users shouldn't have their transaction time affected. Reads happen when the data that is needed is not yet in RAM (in the buffer cache). So ideally, user activity shouldn't be affected often. But when ...


1

(More of a bunch of comments, plus a redundant answer.) Your two examples are not identical -- one is limited to 2015; the other is not. WHERE birthday BETWEEN '2015-04-01' AND '2015-04-01' + INTERVAL 1 MONTH would be able to use INDEX(birthday), but that only covers those who will be born next month. Even if you had a mnth TINYINT UNSIGNED COMMENT ...


2

The whole point of using DATE as a type is so the database can efficiently query the data. It's the same reason you store a number as an INT and not a VARCHAR - so the engine can make intelligent decisions. If you use the LIKE operator on a date, you lose the benefits of having chosen the correct data type. Using MONTH(birthday) allows MySQL to grab the ...


1

I go for SELECT * FROM customers WHERE MONTH(birthday) = 4; since its the common way to select by month


0

This answer has nothing to do with mysql database versions or types, I wanted to know if update statements were making changes AND to do this in my php code.. Created a dummy table with one record and one field which I would query to get the value of mysql's current_timestamp. To the data table being updated, added a timestamp field and used the mysql ...


2

You have a odd system. Your database size is 350+ G and you have 32 bit system I would say this is a system which I would never like to have in my environment. Its very difficult to manage 350 G database on 32 bit SQL Server which has VAS limit(by default) of 2 G. You are bound to face memory pressure going ahead. AWE in 32 bit system only allows SQL ...


1

(Not an answer, but some pitfalls that make it difficult to design this for efficiency.) More than half the users will have only one link. Some users will each have over 100K links. More than half the sites will have only one page. Some sites will each have over 100K pages. What does it mean? It means that any form of indexing, compression, etc, needs ...


1

In almost every use case, InnoDB is preferred over MyISAM. So, yes. To make sure the indexes, etc are converted correctly, see if anything in MySQL to InnoDB checklist needs to be addressed. Note that key_buffer_size should be decreased and innodb_buffer_pool_size increased. In MyISAM, an UPDATE blocks all other operations on the table. In InnoDB, it ...


3

You have a time series (measurements) organized by id (clustered index). I am yet to see a single case where using id as clustered key for time series makes sense. All queries will ask for date ranges. Organize by time: CREATE TABLE measurements ( id bigint IDENTITY, parameter_id int NOT NULL, measuretime datetime NOT NULL, value float NOT ...


1

I think the optimiser is right. When you use INCLUDE, it only stores the included column values on the leaf level of the index, they do not make up the key. So what it is suggesting is that it can decide which branches of the index to scan (measuretime is the key, so it leaves a huge chunk of records out), which means the WHERE doesn't need to test each row. ...


3

You would need an index on each column in order to avoid a sort of every row and column during the ranking process. That would of course introduce significant overhead as scores are updated continuously. Probably not an option unless you have a high-end hardware configuration. The ranking processes could be offloaded onto a read-only copy maintained via ...


2

It's a gradual process and the effect is comparatively small. But of course, looking up entries in system tables gets slower with lots of rows. Those are just regular tables. Highly optimized, but regular tables. There are indexes to keep the effect small. I have never actually noticed an effect in my biggest database with a couple of hundred tables and ...


1

Since the selective predicate on the big table eav_value_text_data big table is v.value = 'rs145368920', you need an index on value more than anything else. The index on attribute is hardly relevant - only in combination with the first to allow index-only scans if possible: CREATE INDEX eav_value_text_data_val_att_idx ON eav_value_text_data (value, ...


3

As Ypercube said in his comment you seem to have an uncorrelated subquery. This means that for each way you are trying to build a line containing 1,000,000 odd nodes. Also it it would be best to put a Geography constructor around the result of the subquery. The tutorial's update statement that you referenced has got a correlated subquery, because the ...


1

All columns on both tables are indexed. Enough said. That is not the right way to go about indexing. WHERE db2.tb2.c1 = db1.tb1.c1 db2.tb2 needs INDEX(c1) -- Keep in mind that a PRIMARY KEY is a UNIQUE key is an INDEX, so do not redundantly add INDEX if you already have PRIMARY KEY(c1). and c6='X' and c9 <='y' and c10>= 'Z' and c12>='N' ...


1

This is caused by Nagle's algorithm. CouchDB sends the HTTP headers and the response body in separate calls, causing the kernel to not deliver the response body for 40ms. As a workaround, you can use this LD_PRELOAD shim to completely turn off Nagle's algorithm for the database process.


1

You should not enable and disable MAXDOP setting on the server using a scheduled job. You should set it away from the default of 0 to some thing sensible for your environment. This script will help you to get a good start. Alternatively, you can use OPTION (MAXDOP N) as a query option. Microsoft provides a good starting point in this KB-2806535 article ...


1

This link is probably a good place to start. Setting the MAXDOP at the server level depends on a lot of things like whether you're using a virtual or physical server, how many physical cores you have, hyperthreading being enabled or not and the number of NUMA nodes you have. But you if you tinker with that, you should be made aware of increasing your cost ...


-2

Create separate index for id: alter table details add key d1(id); To take this index into effect, restart MySQL or analyze table details; If possible, you can also change database to InnoDB for transaction support and other benefits.


0

From the 5.6.3 Changelog: The following items are deprecated and will be removed in a future MySQL release. Where alternatives are shown, applications should be updated to use them. The innodb_table_monitor table. Similar information can be obtained from InnoDB INFORMATION_SCHEMA tables. See INFORMATION_SCHEMA Tables for InnoDB. The ...


0

There is one serious effort to do this - it can be found here. Check out the warning though WARNING It is extremely important for you to fully understand each change you make to a MySQL database server.... &c... It is virtually impossible to factor in all variables - at the end of the day, it comes down to MySQL expertise. The author (Major ...


0

Yikes, I'd be scared to look at your transaction log. This needs to be run in batches. You can experiment with that to determine the fastest runtime. What I like to do is strip out maybe a million or so records, then run updates in various batch sizes, like 1k, 2k, 5k, 10k, at a time to determine the quickest. I've done several hundred million row updates in ...


0

Generally it is better to load an InnoDB table with nothing more than the PRIMARY KEY. Then do one ALTER TABLE to add all the indexes and foreign keys. If you don't have an explicit PRIMARY KEY, that is bad. Change a UNIQUE key to PRIMARY, if practical. But, the foreign keys can be a problem because of dependencies. Plan A: ALTER one table at a time, ...


0

In this particular case, I think the INFORMATION_SCHEMA is a red herring. From my own tests of SHOW COLUMNS performance, the innodb_stats_on_metadata variable doesn't seem to make any difference on either MyISAM or InnoDB tables. However, from the MySQL 5.0 manual... Some conditions prevent the use of an in-memory temporary table, in which case the ...


1

Before start, please note that external keys are usually bad as primary keys, because they are larger than needed, they can change and some people may not have one, or have duplicated ones (even if legally that shouldn't happen). Speaking correctly, the EIN is a code of 9 digits. As such, the technically correct value should be a string. In particular, for ...


0

Wait on dblink is not an idle wait. If your code pull one row after the other, the whole TCP stack is involved. Probably no one changed sqlnet ( see Session data unit SDU) During the long run the remote database is forced to keep track for changed blocks due read consistency ( writes undo). Check if a data replication using materialized view ( MV) in local ...



Top 50 recent answers are included