When I do a filter with WHERE on a View, are the indexes used?
I have a view created from many tables with multiple fields. When I issue a WHERE filter on the View, are the table's indexes in use? ...
mysql
Can't optimize innoDB table
Hello, As my first message here, i don't know if i have to answer or post a new message. I have the same problem as here and the one and only given answer doesn't help me (except if the documentation is incorrect). "You can make OPTIMIZE TABLE work on other storage engines", but my engine is InnoDB. I thank you for your help. S. ...
MySQL include zero rows when using COUNT with GROUP BY
Hi, I am trying to perform a query which groups a set of data by an attribute called type_id. SELECT vt.id AS voucher_type, COALESCE(COUNT(v.id), 0) AS vouchers_remaining FROM vouchers v INNER JOIN voucher_types vt ON vt.id = v.type_id WHERE v.sold = 0 GROUP BY vt.id What I want in the result is the type_id and the number of unsold p...
How to monitor MySQL queries on Windows
Is there any easy way to track/monitor all the SQL of the queries, executed by an MySQL server (on a Windows machine)? Update: I am asking about the free tool. ...
How large to make name fields in database?
There's an old question that asks this very thing, but a lot has changed in databases and unspoken standards. I generally live by the rule as to never make a field size text or memo, even if it speeds up the database. Primarily because someone could flood and possibly hack the db if there are no restrictions in the input interface. ...
How to convert all tables from MyISAM into InnoDB?
I know I can issue an alter table individually to change the table storage from MyISAM to InooDB. I am wondering if there is a way to quickly change all of them to InnoDB? ...
Doctrine, updating layouts and existing data
Hey folks! Please imagine you have an live-application with used data in your doctrine-controlled database. Now you find out that it's necessary to rename some cols and add some others. What is the best approach to accomplish this without too much struggling with your data? Please let my know if it's not clear what I mean, maybe I am ...
Notifying users of database changes
I have, at its heart, a php/mysql application. Every ~15 minutes new data is added to the database. I want to notify users that new data is in the database. I was thinking of having some JS run on the clients computer to check for updates every 60 seconds, but I don't want to pull down the results unless something has actually changed/be...
How to get records from MySQL in c/c++?
This is pretty easy in PHP: $con = mysql_connect("localhost:".$LOCAL_DB_PORT, $LOCAL_DB_USER, $LOCAL_DB_PASS); mysql_select_db("db", $con); mysql_query("set names utf8", $con); $result = mysql_query("select ..."); while($row = mysql_fetch_assoc($result)) { ... } But what's the easiest way to do it with c/c++ in windows? ...
Subsonic, mysql error, Structs.tt, run custom tool
Using VS 2008 and SubSonic and MySQL 4.1 and MySQL Connector 6.2.4... running the custom tool on any of the .tt files receives this exception. Now my guess is this is an issue outside of SubSonic. Error 2 Running transformation: System.NullReferenceException: Object reference not set to an instance of an object. at MySql.Data.MySqlC...
Multi Query PHP Question
I wanted to execute multiple queries in one. Is there a way to condense a bunch of queries into one big string and then execute it? Thanks in advance. ...
SQL to select events that happened at pretty much the same time
I have a database table of events that happened: (timestamp, other data...) I want to group these by things that happened at 'pretty much the same time'. That is, when ordered by timestamp, such that all events in each group are within X seconds (e.g., X=3) of some other event in that group, and more than X seconds from all events in...
Nesting MySQL queries to UPDATE or INSERT with PHP
Using MySQL and PHP I'm making an index of the "amounts" table and grouping it by product, type, month, day, and hour. Then either updating a matching record in the "amounts_merged" table or inserting a new record if it does not already exist. Here is the solution I came up with. But I'm not sure this is the best way to go about this. A...
Is this MySQL query a JOIN, a regular query or both?
I need to get data from 3 different tables. I know the basics about JOINs but when it comes to more complicated queries like getting data from 3 or more tables using JOIN I get a little confused and I just start playing with the queries writing what make sense to me, like the next one: SELECT movies.imdbID, movies.title, movies.year...
Using mysql COUNT(*)
Why is COUNT(*) so much quicker than COUNT(field), Even when field is an index? I am using MySQL ...
Read rows from a mysql table via java
I need sample code to read rows of data from a mysql table using java ...
(MySQL) Greatest Number By group Inner join , performance problems.
2 tables are still very small compared on how they will be in the future. Already on doing tests on some regular queries , performance problems arise. Could someone enlighten me on a better table structure or query ? ==== Table 1 : tv_show ( currently 117 rows) Table 2 : tv_episodes ( currently 43,000 rows ). tv_show contains an ...
Implementing a random chat among people
My idea is to make a website, where people could registry and search for a people to talk. They can choose people from certain country, genre, with certain age and so on. Yeah, I know there is a lot of websites like this, but I want to implement this, because it looks really challenging. Can you give me ideas how could I implement thi...
Mysql table structure and designing
I have two tables both i am working like below concept... When any record will be inserted 'id' of first table ('id' is unique and auto_increment), I want to use this id value for further insertion in my second table (that means id will be foreign key).How should I should to design my table? If i am inserting values and further fetch...
insertion only in table with one auto_increment column
is possible make insert in the table with only one column and this column is primary and auto_increment. exactly what I want is increase id by one and write it to table ... is it possible without select max value and increase it by one and than insert.. I think direct insert just increment value ...