Tag Info

Hot answers tagged

3

The IN operator expects a list of items, not a single string. You need to structure your query as follows: Select * from table where mid(postcode,1,2) IN ('CV','DE','LE','MK','NN');


3

The index IS covering for the second query. However, it is not USEFUL to support the seek. Because neither bar_id nor date_sent is leading the index, the optimiser cannot seek for it. What you have achieved with the covering index is to make table scan faster for the second query. But you have not supported a better seek strategy. This covering index would ...


3

DISADVANTAGE #1 InnoDB only has one Buffer Pool. The benefits of partitioning or compartmentalizing database tables for different clients can still be bottlenecked by database buffers (log buffer, InnoDB Buffer Pool) DISADVANTAGE #2 Scaling becomes dependent on manual operation. This applies to scaling up disks, hardware (disk controllers), and memory. ...


2

What I did MariaDB [test]> select version(); +---------------------+ | version() | +---------------------+ | 10.0.15-MariaDB-log | +---------------------+ 1 row in set (0.00 sec) Are you using 5.5? CREATE TABLE terms_of_use ( id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, current_version varchar(20), last_updated timestamp, ...


2

This is only for Postgres: If the tablespaces reside all on the same harddisk, then there is no performance difference at all. If the tablespaces are created on different harddisks then yes, this can make a difference. In that case the necessary I/O is spread over multiple disks, thus improving the overall throughput (assuming all harddisks have the same ...


2

I am glad you found your own answer. I am sure you found out you can add this to my.cnf [mysqld] expire_logs_days = 14 You do not have to restart mysql. You login as root@localhost and run this: mysql> SET GLOBAL expire_logs_days = 14; To trigger the auto rotate immediately, run mysql> FLUSH LOGS; If you want to zap binary logs from the mysql ...


2

Take a look at my answers to similar questions here (some bits don't apply) and a thread here. Interestingly, RolandoMySQLDBA seems to be discouraging a multi-tenant approach here, yet he gave a +1 to accepted answer in this thread. Rolando? The excellent accepted answer in that thread echoes my own experience. Be flexible, and be prepared to make an ...


2

Within a single instance of MySQL, there are few, if any, advantages in splitting your data into multiple databases and/or tables. With PARTITIONing and InnoDB, you get separate "tablespaces", but there are very few applications that get any performance gain with such. They are listed here: ...


1

You could also use the string comparison operators: SELECT * FROM My_Table WHERE postcode LIKE 'CV%'; You can also make use of the powerful REGEXP operators: SELECT * FROM My_Table WHERE postcode REGEXP '[B-D][U-W]'; (will get the first letter from B to D and second letter from U to W, and so will pick up your desired postcodes - this was just a trivial ...


1

Ok after do some tests and thanks to the comments from Rick James and Farhang Amary from stackoverflow, the following query seems to do the job, so im posting it here for anyone that find itself on the same situation or for anything wrong with it: SELECT P.Person_name AS Person, COUNT(F.id) AS fixes FROM (SELECT person_id, home_work_id, task_id FROM work) ...


1

Interesting problem and I think that I've figured it out. For some reason, MySQL interprets 1e_a_number_ as an exponent - i.e. 10 to the power of something. 1a_a_number has no mathematical meaning, therefore it's not parsed as being a number and accepted as valid. If you notice at the beginning of my experiments below - pure numbers aren't allowed as ...


1

I have used the Percona PAM Authentication Plugin for MySQL to successfully authenticate individual users via LDAP but so far I haven't managed to the group/proxy part working. You do need to make sure that there is a PAM profile for MySQL, which is probably the bit that is missing (first question) from the article you linked. I had also read through that ...


1

The COUNT is too high, correct? That is because the JOINs get more records before the GROUP BY comes along. I suggest you remove the ORDER BY and SELECT more fields so you can see which rows are coming redundantly. That may lead to splitting it into SELECT ... FROM ( SELECT person, COUNT(...) FROM ... JOIN ... ) X JOIN some other tables AND ...


1

MySQL While the MySQL Documentation literally says Typically, the clustered index is synonymous with the primary key, they are not one and the same. Please keep in mind that the clustered index (called gen_clust_index) was created in such a way that the index pages for the PRIMARY KEY and the table's row data coexist in the same pages. Having wide PRIMARY ...


1

When --skip-name-resolve isn't in specified, the IP address of incoming connections is resolved into a name via a DNS query... However, there isn't just one DNS query, because the existence of a reverse DNS entry (e.g. a PTR record 203.0.113.113 → foo.example.com) is not sufficient for hostname-based authentication -- anybody could change a reverse ...


1

The ODBC Connection needs to be a System DSN. The connection is made as the service account, not you, and so the service account doesn't know about your User DSN. From the article: Select the System DSN tab to configure a data source for the entire system.


1

I don't think the problem is with your query, but rather with your data. Check out the error message It ssays : #2014 - Commands out of sync; you can't run this command now and 1690 BIGINT UNSIGNED value is out of range in '(bt.end_val-bt.start_val)' I think for some reason, now (bad data entry?), your query is trying to insert a negative value ...


1

If you are using a DB that doesn't support CTEs (such as mySQL currently) or the query planner doesn't can't optimise what richardtallent's solution is trying to do so ends up scanning instead of seeking on user_id or event_timestamp (I suspect it MS SQL Server's planner may end up doing this), you could try unrolling the CTE a little and using it as a ...


1

What's below works for your data - be careful of any variation in the field width - that's why delimited files are better - csv, or better still, if you can get it, pipe-delimited (|). It also successfully deals with the header line. I did the following: My code will do for both MD5 and SHA2 passwords - uncomment the relevant bits. CREATE TABLE my_user ( ...


1

My blind guess would be to look in %APPDATA%\MySQL on your system as follows: cd %APPDATA% cd MySQL dir .my_secret For those who use the MySQL no-install Zip File (such as myself), you will not see one. If .my_secret is not on your Windows servers, I have an alternative. You could start up mysqld manually with skip-grant-tables, set the password net ...


1

I assume you mean you want to find the "first" row with same Ticker_ID and same or bigger DateTime, first when ordered by DateTime ascending. One way to do this, assuming that the price table has a unique constraint on (Ticker_Ticker_ID, Price_DateTime): UPDATE flaggedcomment AS fc INNER JOIN price AS p ON p.Ticker_Ticker_ID = ...


1

1) Any INSERT, UPDATE, ALTER etc type Query executed on the Master will be written to the Binary log, causing the MASTER_LOG_POS to increase Furthermore, if the Master is receiving updates from another Master (in a Master to Master setup), AND if you have LOG_SLAVE_UPDATES enabled these will be written to the Binary Log as well. Each time you restart the ...


1

Sample data: CREATE TABLE v (`vote` varchar(8), `game` int, `year` int) ; INSERT INTO v (`vote`, `game`, `year`) VALUES ('player 1', 2, 2015), ('player 1', 2, 2015), ('player 2', 2, 2015), ('player 2', 2, 2015), ('player 2', 2, 2015), ('player 3', 2, 2015), ('player 1', 2, 2015), ('player 3', 2, 2015), ('player ...


1

If you ONLY want to optimise for this query. This is the best index: ALTER TABLE items ADD INDEX (category, created_at, user_id) This optimises the value of the filters, which reduces the total amount of data you touch. By adding user_id, item_id at the end of the query, you make the index covering and it saves you a lookup into the primary index. We can ...


1

SQL Server version of 'check which tables a user have access' (Database Specific) SELECT [UserName] = princ.[name], [PermissionState] = perm.[state_desc], [PermissionType] = perm.[permission_name], [ObjectType] = CASE perm.[class] WHEN 1 THEN obj.type_desc -- Schema-contained objects ...


1

Does mysqldump have an option that allows multi-valued inserts to be batched for each table? Yes. net_buffer_length - The initial size of the buffer for client/server communication.



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