Hi I am working on a query that is using the below schema to find out how often users are doing searches by company (the app basically allowed you to do a search by company) SEARCH_LOG ---------- date_of_search (DATETIME) company_id (INT) COMPANIES --------- id (INT) company_name (VARCHAR) (there are more columns but these are the re...
Something I've noticed recently is people using IN where it seems kind of inappropriate. I'm feeling like I'm missing a trick - perhaps there are speed gains or something? This query: SELECT * FROM pages WHERE is_visible IN ($visibility) Why use that? Why not: SELECT * FROM pages WHERE is_visible = $visibility Sure, if it were thi...
How do I define relation rules in mysql? Table1: bookId authorId bookName Table2: authorId authorName This is how I would like the Mysql server to behave: When I try to insert a row into table1 with a authorId that doesn't exsist in table2, mysql would produce a error. ...
I'm working at a complex script which could be processing upto 500,000 records. Here's my question. Basically my code will parse a text file to get each of those 500,000 or so records. Each record will have a category, my code will need to check if a new record in the categories table had been created for that category during that parti...
I've been looking for a good Excel file generation class and have yet to find one - my number one issue is that I although I can get the exported file to open in Excel (running 2007), I always get a warning saying "the file is in a different format to the file extension". I noticed that the Excel export in phpMyAdmin even generates the ...
When I optimize my 2 single queries to run in less than 0.02 seconds and then UNION them the resulting query takes over 1 second to run. Also, a UNION ALL takes longer than a UNION DISTINCT. I would assume allowing duplicates would make the query run faster and not slower. Am I really just better off running the 2 queries separately? I w...
Hey, We're using MySQL with Innodb Engine storage. We have an "evented" environment that sends multiple concurrent requests on a table. Basically, it works like this: We have a find_or_insert function that does that: - find() -> on result, if empty -> insert -> on result find() We're using a non-blocking MySQL driver, so basically, wh...
In looking at similar questions like: http://stackoverflow.com/questions/740182/getting-trigger-to-insert-changed-column-values-only-in-audit-table http://stackoverflow.com/questions/617225/audit-logging-strategies I would like to take this one step further and compare the updated data to see if it actually has been updated... I'm not...
I asked how to do this before but it seems I needed to put more code to really get an answer. I have a reddit type site, and I am trying to create a rss feed this is the code, but I get a Fatal error: Uncaught exception 'Exception' with message 'Query failed' Here its the code: <?php require_once($_SERVER['DOCUMENT_ROOT'].'/config.php...
I've wrote a quick PHP class to ease the access to a mysql database. The class works ok, and has a query() method that opens up the connection, executes the query and then closes the connection (I know that the connection is supposed to close by PHP itself after the script finishes, but I don't like to rely very much on that). From a pe...
I am trying to write an automated test script for testing a MySQL Event that I created, and I am wondering if there is a way to execute some kind of command to force the Event to execute immediately. The Event is set to run daily at midnight, but I don't want the automated test script to have to wait for the Event to trigger. One way I...
I want get the time used for a case so I can create an overview. The SELECT query for the cases looks like this: SELECT bc.id, bc.title, bc.estimateCurrent FROM cases bc For one case I can get the used time like this: SELECT SUM(TIME_TO_SEC(TIMEDIFF(dateEnding, dateBeginning))) AS calculatedTime FROM timesheet WHERE `#case` = ? ...
Hi, I have a table with devices, their buyed country and their buyed date. I want to retrieve : - the total number of devices by country - and the number of devices buyed since 1 month for each country. I use this request to make this : SELECT countryCode, COUNT(*) AS sinceBeginning, ( SELECT COUNT(*) FROM mytable WHERE countryCode...
When using Grails 1.1 together with a MySQL the charsets of the auto-generated database tables seem to default to ISO-8859-1. I'd rather have everything stored as pure UTF-8. Is that possible? From the auto-generated database definitions: ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1; Note the "latin1" part. A work-around th...
Need to provide this facility on a button-click in my web application. Could be done using mysqldump, but for that I'd have to start an external process? (the command prompt) Is there a simple SQL query that lets us backup and restore databases? (particularly for MySQL) ...
Hi, I'm new at WPF and C# and I'd like to know the best way of accessing MySQL. Googling a little bit I've seen there's a Linq provider for MySQL. Is that the best way? http://code2code.net/DB_Linq/ I've never used Linq before so I'll start today unless somebody knows a better way of doing that. Thanks in advance ...
Hi! I have one stored procedure in while I am getting comma separated values in parameters. I have three parameters which has comma separated values. and i need to put them in table's columns so I am using while loop. but i am scared when too many(say lakhs of users) users will connect to my website then my procedure will have performanc...
Hi GUys, I am using Java and Mysql for a Program, I am Using a Script File in order to restore a Databsae. Under Java I am Executing a command:under bin: mysql -u root -proot test< c:\test.mysql It is not running while If I run it under cmd line it will execute properly and restore the database. Is anybody there who knows.. why it hap...
We're currently building a website with a categorized MySQL table containing various competences, and we noticed that the nested set model would be optimized for this. Although, we've got a pretty serious problem - the nested set model doesn't allow any sorting, and we really need that possibility. I'd like the output data to be array(id...
Let's say I have two or more tables filled with users and I want to retrieve all users from those tables in the same query. Tables share some columns and those columns that have the same name are the ones I'm trying to retrieve. Something like: SELECT name, age FROM users1; SELECT name, age FROM users2; etc. Note: this is just an ex...