I have noticed in phpMyAdmin you can click 'optimise' on tables, and it runs: OPTIMIZE TABLE table_name What does this actually do? Should I have some cron job running daily that runs OPTIMIZE TABLE on my tables? Thank you ...
I want to connect and execute one (or sometimes several) SQL statements, and NOT have those replicated to the slaves. I have no replicate-do or replicate-ignore configs, so I can't use some non-replicated database to send the commands from. And I know about: set global sql_slave_skip_counter = 1 But that's on the slave. I'd like to...
Sorry about the vague subject but I couldn't think what to put. Here's my problem, I'm doing a query on a table that returns me a count of items related to a day. I want to make sure that if I do a query on the DB, I always get a set number of rows. For example, imagine I have the following table that contains a log of when people log...
Hello, I have the following UTF-8 file exported from a microsoft access file http://www.yousendit.com/download/TTZtT214SU84Q1FLSkE9PQ I have ensured my mysql database is utf8 with the status; command for both client and server. I insert the above file into my database with the following command: LOAD DATA LOCAL INFILE 'tblAuction1.tx...
In MySQL, using statement based replication I assume that every SQL statement gets executed on the slave exactly the same way as on the master. However, when I run the statement: LOAD DATA INFILE 'filename' INTO SomeTable; I'm not sure if the file needs to exist on the slave the same way at it does on the master. Can someone point me ...
Is there an easy way to run a MySQL query from the linux command line and output the results in csv format? Here's what I'm doing now: mysql -u uid -ppwd -D dbname << EOQ | sed -e 's/ /,/g' | tee list.csv select id, concat("\"",name,"\"") as name from students EOQ It gets messy when there are a lot of columns that need to be...
Coming from another question of mine where I learnt not to EVER use db queries within loops I consequently have to learn how to fetch all the data in a convenient way before I loop through it. Let's say I have two tables 'scales' and 'items'. Each item in items belongs to one scale in scales and is linked with a foreign key (scaleID). I...
I have a multi-threaded program whereas each thread, at the start of execution, acquires a single connection from a MySql connection pool class. The thread then uses a number of database table wrapper classes to do its business logic by passing its database connection to the wrapper classes. The operations on these wrapper classes are no...
I want to select a blob col from one table, base64 encode it and insert it into another tables. Is there any way to do this without round tripping the data out of the DB and through my app? ...
What's the best way to handle many-to-many trigger relationships like the Twitter "follow this user" problem. I have a similar problem with users "watching" threads for replies. If there are 10,000 users watching a thread, and someone replies, how best to notify the watchers? All I can think of is the following: Upon insert, check th...
I have a mysql database set as utf-8, and csv data set as utf-8, delimited by semicolons and enclosed by double quotes. The data Is seemingly imported fine, when doing a direct dump from the database. However when attempting to display one of the fields containing html by echoing out in PHP, part of the html code is displayed instead o...
I have the following SQL query: select expr1, operator, expr2, count(*) as c from log_keyword_fulltext group by expr1, operator, expr2 order by c desc limit 2000; Problem: The count(*) as part of my order by is killing my application, probably because it don't use index. I would like to know if there is any way to make it faster, l...
Hi, i would to know how do you determine the hardware needed for a server?, now i have a DB Server in MySQL, now i want to buy a server, but i don't know what is the hardware that i need. This is a server for a web page. EDIT: The software it's just MySQL Server ...
I have been tasked with creating a new frontend for a legacy website. It is written in php (pre-oo), and uses a MySQL database. The hosting provides a .Net package, but does not offer Ms Sql Server. This is fine, as the database is working fine, but I really want to use Asp.net for the pages. However, most tutorials I've seen on conne...
Lets say I have two tables - "child" and "parent" with many-to-one relation. What I need is to delete child entries if parent record is deleted. It is not a problem if I link child table from parent by creating one-to-many association in parent.hbm and set cascade="all-delete-orphan". The problem is I don't want one-to-many relation o...
I need a high performance framework in native C++ for SQL. I need it to be able to use MySQL, Oracle and Microsoft SQL Server and provide abstraction from the lower level problems/idiosyncrasies found in every different syntax required for by DBMS from different vendors. Something like LINQ for C# and VB .Net. ...
Hi I have a table that looks a bit like this actors(forename, surname, stage_name); I want to update stage_name to have a default value of forename." ".surname So that insert into actors(forename, surname) values ('Stack', 'Overflow'); would produce the record 'Stack' 'Overflow' 'Stack Overflow' Is this possible? Thank...
In postgres I am fairly sure you can do something like this SELECT authors.stage_name, count(select id from books where books.author_id = authors.id) FROM authors, books; Essentially, in this example I would like to return a list of authors and how many books each has written.... in the same query. Is this possible? I susp...
Hi, Previous programmer left me with "beautiful" piece of code and he kind of forgot to apply something to it. There is a query which selects several items from several tables. 6 Items can be chosen. It means 6 tables can be chosen, however there can be more tables - even 20 of them. I need to get that list of tables from processed que...
I'd like to implement a bloom filter using MySQL (other a suggested alternative). The problem is as follows: Suppose I have a table that stores 8 bit integers, with these following values: 1: 10011010 2: 00110101 3: 10010100 4: 00100110 5: 00111011 6: 01101010 I'd like to find all results that are bitwise AND to this: 00011000 Th...