mysql

Is using rand() in an INSERT statement also slow?

The MYSQL rand() function is notoriously slow in select statements, is this true for insert statements as well? I would like to insert a new row with random number in the following way: insert into new_table (field1, field2, randomField) values ('Hello', 'Ola', rand()); Will the rand() function become slow as the table gets larger a...

Is it possible to insert data into a MySQL view?

I made a MySQL view with 4 tables. Is it possible to insert data into the view and have MySQL automatically pass the data into the right table? ...

how to get mysql command line client not to print blob fields in select *

Exploring some tables which have blob fields. How could I do a select * with the command line client and have it surpress the printing (or truncate to a standard field width) the blob fields rather than scrolling a bunch of binary junk on the screen? This is with mysql 5.1 client. Just want to do a select * and not list all of the non-...

Using parameters with ADO Query (mysql/MyConnector)

Today I downloaded and installed MyConnector so I can use Mysql with ADO, everything installed, OK!, I can make connection with ODBC and do a connection from my delphi environment. when I build my Query at runetime, I get an error saying : --------------------------- Debugger Exception Notification --------------------------- Project P...

Why navicat writes key instead of foreign key in CREATE TABLE?

CREATE TABLE IF NOT EXISTS `trl_bill` ( `bill_id` int(11) NOT NULL AUTO_INCREMENT, `fee` double NOT NULL DEFAULT '0', `begin_date` date NOT NULL, `end_date` date NOT NULL, `is_paid` tinyint(4) NOT NULL DEFAULT '0', `request_id` int(11) NOT NULL, PRIMARY KEY (`bill_id`), KEY `request_id` (`request_id`) USING BTREE ) ENGINE...

MySQL: Include COUNT of SELECT Query Results as a Column (Without Grouping)

I have a simple report sending framework that basically does the following things: It performs a SELECT query, it makes some text-formatted tables based on the results, it sends an e-mail, and it performs an UPDATE query. This system is a generalization of an older one, in which all of the operations were hard coded. However, in pushing...

MySql Select statement distinct latest date value based on Primary ID of Keyword

Basically I have a list of SEO keywords in one table and their Ranking including Date entered in another table. keywords [keywordID(PKID)] [keyword] ---------------------------- 1 SEO 2 Vancouver Homes keywordRanking [rankingID(PKID)] [keywordID(FKID)] [ranking] [rankingDate(timestamp)] --------------...

Select DISTINCT from multiple columns of multiple tables with PHP and MySQL

Using PHP and MySQL I'm trying to get all the distinct values out the number and type columns from all 4 of the tables listed below: table_1 ID|t1_number|t1_type 1|1|new 2|1|old 3|2|new 4|3|new 5|1|old table_2 ID|t2_number|t2_type 1|1|future 2|1|new 3|3|past 4|3|new 5|1|new table_3 ID|t3_number|t3_type 1|1|past 2|1|new 3|1|new 4|1|...

Recover data from a dropped/truncated MySQL table

I just spend a few hours putting together a table full of data, and just decided to drop a WP installation in the same database. I expected WordPress to leave my table as it was and simply add the WP_* tables, but apparently the install wiped my database before installing the wordpress tables. I have full access to the server root. Is t...

Database testing

Currently doing a test plan for a system. May I know what are the ways to test a database? How do I test whether the database is a good database? And how do I stress test a database? ...

Matching delimited string to table rows

So I have two tables in this simplified example: People and Houses. People can own multiple houses, so I have a People.Houses field which is a string with comma delimeters (eg: "House1, House2, House4"). Houses can have multiple people in them, so I have a Houses.People field, which works the same way ("Sam, Samantha, Daren"). I want ...

MySQL error: Can't find symbol '_mysql_plugin_interface_version_' in library

The boring, necessary details: I'm on Snow Leopard running MySQL locally. I'm trying to install the Sphinx engine for MySQL like so: mysql> install plugin sphinx soname 'sphinx.so'; ERROR 1127 (HY000): Can't find symbol '_mysql_plugin_interface_version_' in library I've Googled everywhere and can't seem to find an actual solution to ...

selected responses on form are not picked up on 2nd php page

I have a section of code that I'm stumped on how to handle. The Selected options on the drop down menues are not being picked up on the second PHP page. That is to say the variables error, error1 & error2 on the second page remain blank. I had the same issue with other variables, but was able to fix them with $_POST, but not sure how ...

MS SQL database Admin/IDE

I'm using MS SQL for a project, but have always used MySql in the past. MySql has a really nice interface which lets you build queries, offers help, backup etc. What free programs are there for MS Sql that can do this. I'm currently a student so I have free access to MS Sql but the only interface I have for it right now is Visual St...

Code igniter MySQL AES with active records?

Hey, How would i use code igniters active records to insert/update/select data from a database using mysql's built in aes encrypt/decrypt functions? I know i could just use the normal sql query, but i'd like to use active records ideally. Thanks ...

Insertion speed slowdown as the table grows in mysql

I am trying to get a better understanding about insertion speed and performance patterns in mysql for a custom product. I have two tables to which I keep appending new rows. The two tables are defined as follows: CREATE TABLE events ( added_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, id BINARY(16) NOT NULL, body MEDIUMBLOB, UNIQUE K...

INSERT query is not letting me post data to database

I'm trying to implement a comment.php script that takes the data from a html textfield and save it to a database in phpmyadmin. I implemented registration.php, which is longer and it inserts new users flawlessly. Now the comment.php, which is simpler is not adding anything to a comments table. Here's the code for comment.php: <?php ...

How make this sql. ( conditions sql )

Query: SELECT * FROM Post ORDER BY Post.rating <-- Here , i want ( Post.rating * 5 ) when Post.recommended = 1 ) Thanks all people. ...

RE: How make this sql. ( conditions sql )

I questioned here this: http://stackoverflow.com/questions/3827690/how-make-this-sql-conditions-sql but now, I want SELECT SUM( Post.rating ) as countRating <--- THIS i want Post.rating * 5 WHEN Post.recommended = 1 FROM posts as Post ORDER BY countRating DESC help me ...

Problem with restoring MySQL database - mysqldump question

I made a backup of my_database like this: mysqldump --add-drop-database --databases --user=my_username --password=my_password my_database > backup.sql Then I deleted the database completely, and tried to restore it like this: mysql --user=my_username --password=my_password my_database < backup.sql I got the following err...