View Slow Query Log

Slow View Queries
I have a fairly complex view which, when you execute a SELECT * shows all info very quickly (0.24 seconds to return nearly 3000 rows). However, when I start throwing slightly more complex queries at it, things really slow down - an average query can take 40 seconds to execute.

---

View Last Query Executed
I was wondering if it was possible to obtain the last query executed on a particular table? I have a request to log the queries executed on a particular table for audit purposes, but not sure if this is possible?

If I was hard coding the query before hand, I could obviously use this. However I am using php PDO connections following:

Quote:

$update = $db->prepare("UPDATE `table` SET {$fields} WHERE `id` = :id ");
$update->bindParam(':id', $data, PDO::PARAM_INT);
$update->execute();

The above is a simplified version, which is dynamically building the query. Essentially however I cannot obtain the whole query this way, so wondered if MySQL stored it ?

---

Query/View In MySql?
I am new to MySql (version 4.1). I used to use Views in Oracle that I can call them directly from my application. It seems like I can't do that with MySql. If I have a complicated query to run, I have to write the sql statement explicitly in the application each time I want to run that query. Is that true? Or am I missing something?

---

View Query Logs
I have a db set up and it looks like nothing went to the db because of some errors. How do I open the query log to see if anything was registered.

---

Slow Query
i have this query on a website/webapp that has expanded beyond all expectation. It now takes nearly 30secs to return results from the database

SELECT cl_t.Client_ID, Buyer_1_Title, Buyer_1_Prename,
Buyer_1_Surname, Tel_No, Mob_No, Buyer_2_Title,
Buyer_2_Prename, Buyer_2_Surname, Email_Add,
Price_Max, MAX(activity_t.Date) AS lastcomm
FROM cl_t
INNER JOIN cl_want_t
ON cl_t.Client_ID = cl_want_t.Client_ID
AND Agency_Code ='$agencyloggedincode'
AND Deleted = 'N'
LEFT JOIN activity_t
ON Buy_Sell = 'B'
AND Ref_No = cl_t.Client_ID
WHERE cl_t.Sales_Agent_ID = $agentid
GROUP BY cl_t.Client_ID
ORDER BY $order
The problem is the call to MAX(activity_t.Date) AS lastcomm

activity_t holds all known contact with all known clients and as such is a very large table, the call to search through all of these records and return only the date of the last entry for this client is taking the time. If I remove this from the query I get results in 3 seconds.

I have indexing on activity_t.Date & activity_t.Ref_No

Question, is there a way of doing this quicker within the table I already have, or should I create another table that just holds the last update date for each client, and get the date from this much smaller table.

---

Why Is This Query Too Slow?
I find this query to be exceptionally slow(around 2.5 seconds), could some tell me why this is so?

MySQL
SELECT st.profile_views,count( DISTINCT p.ID ) news_submitted, count( DISTINCT pv.ID ) news_voted, count( DISTINCT pcom.ID ) news_commented, u.joined, u.weight FROM users u LEFT JOIN posts p ON p.submitted_user_id = u.user_id LEFT JOIN post_votes pv ON pv.user_id = u.user_id LEFT JOIN post_comments pcom ON pcom.user_id = u.user_id LEFT JOIN stats st ON st.user_id=u.user_id WHERE u.user_id='john' GROUP BY u.user_id
I traced the cause to this line
count( DISTINCT p.ID ) news_submitted (from LEFT JOIN posts p ON p.submitted_user_id=u.user_id)
But when i execute something like this

MySQL
SELECT count( DISTINCT p.ID ) news_submitted FROM posts WHERE submitted_user_id='john'
it is quite fast (around 0.03 seconds)
So why does it slow down when i'm joining the above query with 3 other tables ?
Should i use INTEGER for user_id instead of string like 'john'?

---

Slow Query Log
my slow log is catching a slow query, however the timestamp for the query is "0". I also placed a timestamp on the query to echo out to the results page, and it is about 4 thousands of a second. Why is it showing in the slow log?

---

Slow Query Using NOT IN
I am migrating a MSSQL server to MySQL. I know the following SQL is valid for both servers, but MSSQL finishes execution of the query almost instantly, and MySQL has been running the query for the past ten minutes and still is not finished. There is basically the same amount of data in each database. Does anyone know ....

---

Slow Query
I have a query that is running really slow !!!!
I have joined on Key fields and indexed the tables fully but it is still solw.
--------------------------------------------

select d.id, a.signed, u.Forename, u.Surname, d.paid, p.date, d.payment, p.amount, d.acctual
from details d
join poten a
on a.id = d.id
left join recieved p
on d.id = p.id
left join users u
on a.signed = u.userid
where d.paid > '01-Dec-2005'
and d.authorrceived is not null
and d.authorrefused is null
and ((d.payment starting 'E' or
d.payment starting 'e') or
(d.payment starting 'Q' or
d.payment starting 'q' and
p.target = '500'))
order by d.paid, a.signed, d.id

---

How To View Arabic Data In MySQL Query Browser
I am making a website on linux and i am using a program called rekall to view the database and also the table was created using this program.

most of the data in the database are in Arabic and rekall views the data in an acceptable manner with some errors and without any configuration

but when I tried MySQL Query Browser on windows and several other programs including navicat, toad, heidiSQL all the arabic data was not viewable except the data in blobs which can be exported to extenal files

The website encoding is [ar-eg] and [utf-8]
I want to view the arabic data stored in the table using a windows program
and if the program is fast and simple it would be much better as I am not the one who is going to use it

---

Slow Update Query
I have about 2000 update queries to do, which takes about 1 hr on 250,000 rows.
My table is getting kinda slow here is the query i am using
UPDATE nametable SET
sectionname = replace(sectionname,'".$oldsec."','".$sec."'),
categoryname = replace(categoryname,'".$oldcat."','".$cat."'), published=Ƈ'
where sectionname='".$oldsec."'
and categoryname='".$oldcat."' ;

I am wondering if the same thing is possible with an insert... on duplicate key statement?

I cant seem to get the insert statement to work, but not even sure whether it is appropriate.

With this query I am basically finding and replacing some columns based upon another table (within the php script I am using)

---

Slow Query Log Files
I have edited my.ini file to create a file called slow queries. My problem is that when the server starts up i do not get a full list of all slow queries. Is there anything else i need to change? How else can i come about in getting all queries that took a long time to execute? im using mySQL 5.0.9.

---

Slow Query Log Not Staying On
I'm running MySQL 4.0.16 on Windows 2003. I just added the mysqld-nt
command line option to enable the slow query log, started MySQL, and the
option showed up as turned on. Then later I restarted the server, and the
slow query log option went back to being turned off. Is this a Windows
problem in not remembering the service parameter? Has anyone else seen
this?

---

Query With 3.3million Rows Is Slow?
I'm not that great with MySQL...so I was hoping someone could help me out.
The query I'm running is too slow...can anyone tell me what I can do to
speed it up..if I can at all? I was wondering if because ZipListMatrix has
3.3 million rows that 8 seconds is all the faster it's going to be. Any
help is greatly appreciated! I have already "optimized" the tables.

---

Can't Turn On Slow Query Logging
long_query_time = 1
log-slow-queries = /var/lib/mysql/slow_queries.log
Is the above syntax not correct for enabling slow query logging? All examples I've seen have the dashes in the second variable and underscores in the first.

When I restart MySQL with those lines in my.cnf, it fails to start, but writes nothing to its error log.
/var/lib/mysql/slow_queries.log exists, is owned by mysql, and has read/write permission.

---

Turning On Slow Query Logging?
Background: I paid a young admin set me up on a database server. He installed the basic I needed for the server...at my request...No Cpanel...mysql and apache and some tight security w/o even a domain name to SSH into. Unfortunately, he's a busy kid, and teens sometimes don't realize that people depend on them...and well, I can't really seem to get him to do much so I gave up and figured it's a good way to force me to learn all this myself...
Well anyway, now I want to turn on Slow Query logging. But before I do that, I need to know how MySQL is running. Is SQLogging turned on already? Where is it logging to? So first thing I want to look up is, when the server is rebooted, what's the command to restart mysql? No clue. How do I change the setting? And of course, the server is production, so when I make the change, it needs to be quick, it needs to be smooth, and I need to be able to roll back to the previous config if necessary.
I'm running Redhat Enterprise.

---

Help Rewriting A Slow Phpbb Query
I have a "glance" or "Recent Topics" list on my forums that have become fairly complex. I modified an already feature rich glance mod to allow users to select individual forums to exclude from showing topics in the list. As well when users are members of certain forum groups, they see topics from the group forum in the list, and they are highlighted a different colour.

The main SQL query to create the list often is showing up in the MySQL Slow_query log and I'm pretty sure is the main cause for the page loading slow.

I am no mysql Guru, so I thought I would seek the advice of some to improve or totaly rewrite this slow query.

PHP

$sql = "SELECT     
    f.forum_id, f.forum_name, t.topic_title, t.topic_id, t.topic_last_post_id, t.topic_poster, t.topic_views, t.topic_replies, t.topic_type,
    p2.post_time, p2.poster_id,
    u.username as last_username,
    u2.username as author_username
FROM "
    . FORUMS_TABLE . " f, "
    . POSTS_TABLE . " p, "
    . TOPICS_TABLE . " t, "
    . POSTS_TABLE . " p2, "
    . USERS_TABLE . " u, "
    . USERS_TABLE . " u2                
WHERE
    f.forum_id NOT IN (" . $forumsignore . $glance_recent_ignore . ")
    AND t.forum_id = f.forum_id
    AND p.post_id = t.topic_first_post_id
    AND p2.post_id = t.topic_last_post_id
    AND t.topic_moved_id = 0
    AND p2.poster_id = u.user_id
    AND t.topic_poster = u2.user_id
ORDER BY t.topic_last_post_id DESC";
$sql .= ($glance_recent_offset) ? " LIMIT " . $glance_recent_offset . ", " . $glance_num_recent : " LIMIT " . $glance_num_recent;


The "NOT IN" list varies per user, but here is an example:
NOT IN (77,75,76,25,26,37,63,64,66,67,67,1,25,26,37,70,28,75,76,78)

---

Slow Query W/ Join & Ordering
I am trying to figure out why I have a hugely slow query (~2 seconds in my testing environment). Details are below:

It involves two tables, products and vendors.

Products is a huge table, so I will only include the (ostensibly!) relevant fields in its description:

CREATE TABLE `products` (
`id` int(11) NOT NULL auto_increment,
`vendor_id` smallint(6) NOT NULL default Ɔ',
`product_code` varchar(255) NOT NULL default '',
`internal_name` varchar(255) NOT NULL default '',
`lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

PRIMARY KEY (`id`),
UNIQUE KEY `product_code` (`product_code`),
KEY `vendor_id` (`vendor_id`)
) ENGINE=MyISAM;
Vendors are much more straightforward:



CREATE TABLE `vendors` (
`id` smallint(6) NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;
The following query executes in no MORE than 0.01 seconds:


SELECT DISTINCT p.id
, p.product_code
, unix_timestamp(p.lastmodified) as lastmodified
, p.internal_name
FROM products as p
ORDER BY p.product_code ASC
LIMIT 0, 30;
And has the following attributes:

+----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+
| 1 | SIMPLE | p | index | NULL | product_code | 257 | NULL | 25124 | Using temporary |
+----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+
When I join with the vendors table, so that I can fetch the vendor's name for each product, I use the following query, which takes about 1.88 seconds:



SELECT DISTINCT p.id
, p.product_code
, unix_timestamp(p.lastmodified) as lastmodified
, p.internal_name
, v.name as vendor_name
FROM products as p
LEFT JOIN vendors as v ON v.id=p.vendor_id
ORDER BY p.product_code ASC
LIMIT 0, 30;
It has the following characteristics:

+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+
| 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 25124 | Using temporary; Using filesort |
| 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 2 | te_inventory.p.vendor_id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+
Note the addition of the filesort. I'm unhappy enough about the temporary, which I don't really understand, but the filesort is, I'm fairly sure, killing me.

Closer investigation (or maybe just common sense if you aren't a MySQL newbie like me) shows that the ORDER BY clause is responsible, for when I join without the ORDER BY, my query time goes back down to 0.01 seconds or so:



mysql> explain SELECT DISTINCT p.id
-> , p.product_code
-> , unix_timestamp(p.lastmodified) as lastmodified
-> , p.internal_name
-> , v.name as vendor_name
-> FROM products as p
-> LEFT JOIN vendors as v ON v.id=p.vendor_id
-> LIMIT 0,30;
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+
| 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 25124 | Using temporary |
| 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 2 | te_inventory.p.vendor_id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+
Any clues on how I can get the execution time to go down when I am sorting? I'm also curious why MySQL is using a temporary table,

---

Mysql 5.0 - Using My.cnf - Unix / Slow-query-log
I installed mysql 5.0 and need to set up slow-query-log and other logging options.

Here is what I did. But I dont see it working yet.

1. cd /var/db/mysql
2. chown mysql slowquery.log
3. touch /usr/local/etc/my.cnf
4. chown mysql /usr/local/etc/my.cnf

vi my.cnf

[mysqld_safe]
-u mysql
--log-slow-queries=/var/db/mysql/slowquery.log

so now when I type:

mysqladmin shutdown
and than

mysqld_safe &
my sql restarts but the log files are not being used.
also - how do I know if my my.cnf is being used at all?

---

Federated Tables Slow? (like 4.5 Hrs For A Query)
I've got a problem with federated tables. I'm using MySQL 5.1 (with InnoDB as the default table type) on a Win2K server, on which I've got four federated tables pointing at four MyISAM tables on a MySQL 4.1.11 server. Of the four tables, three of them run just fine, and I can retrieve data quickly with no problems. The fourth is a sheer pig. While they have different columns, all four tables are roughly as complex as each other, all having the same features and developed by the same team.

The most obvious difference, and what I suspect might be the problem, is that the first three tables have between 150 and 1,000 records, the fourth table has closer to 15,000. Still, there isn't that much lag when I'm pulling from the smaller tables, and the lag is really serious when I'm pulling from the larger one; I ran three queries last night to test, and I could pull data from the smaller tables in about 5 minutes, but the larger table took 4.5 hours- possibly because it was joined with two other tables, but the joins on the smaller tables didn't cause this kind of problem.

The second obvious difference is the fact that I'm pulling from a MyISAM table into a federated table... from which I would like to store into an InnoDB table, but it ends up timing out quite a bit.

Connecting to the database I've federated to isn't a problem. It responds to a PHP frontend lightning-fast. It's just my federated tables that suck so bad. I wouldn't even use federated tables, but I need to pull from the MyISAM database for storing historical records of inventory. What am I doing wrong, and what can I do to speed things up?

---

Slow Inefficent Query (FIXED)
I have this query which pulls members from a table of 1200 members details, filtering out those without and email address and those that don't want Newsletters, and also any whose email address exists in a second table (emails that have hard bounced previously)

My first attempt took an age and returned about 10,000 members, clearly loads of repeated rows

My 'final' attempt with a DISTINCT shoved in works perfectly but is really slow, PHPAdmin says it takes 1.1s but on the website the data doesn't appear for over 30s, all the other sundry queries on the same table work nice and quick on the same website using the same php to generate the table of results

The googling I've done suggests (to me) that there's nothing wrong with the query but I suspect that there's far too much checking of fields going on from the pre DISTINCT query's results ....

---

VER VERY VERY Slow MySQL Query HELP URGENTLY NEEDED
I have the following MySQL query, but it is VERY VERY slow and seems to be crashing the server. There are 300,000+ records in the 'tracker' table.

SELECT sites.*, SUM(if(tracker.type='view',1,0)) AS numberOfViews, SUM(if(tracker.type='click',1,0)) AS numberOfClicks, SUM(tracker.revenue) AS totalRevenue FROM sites LEFT JOIN tracker ON tracker.site_id = sites.id GROUP BY sites.id ORDER BY sites.domain_name

---

Why Does The Slow Query Log Show More Rows Than Exist?
# Time: 070528 17:14:57
# User@Host: counter[counter] @ localhost []
# Query_time: 3 Lock_time: 0 Rows_sent: 7 Rows_examined: 120647
SELECT SQL_CACHE `webpageUrl`, `webpageName`, COUNT(*) AS `count`, (COUNT(*) / (SELECT COUNT(*) FROM _1_log)) AS `pct` FROM _1_log GROUP BY `webpageUrl` ORDER BY `count` DESC LIMIT 7;

mysql> select count(*) from _1_log;
+----------+
| count(*) |
+----------+
| 111824 |
+----------+
1 row in set (0.00 sec)

---

Large Table, Slow Query Question
I have a table with ~800,000 records. I need to grab random rows from the table based on certain criteria. The problem is that average lowest subset to grab the random row is around 200k. Here is what I'm trying to do:

There are 4 columns: data,n1,n2, and n3. I need to get the value of the data column based on criteria using the n1-n3 columns.

The most common query is SELECT data FROM table WHERE n1 = ?

The problem is that n1 can be only 1 of 5 possiblities. When the table is finished being populated there will be roughly 1.5 million records and 250k for each value of n1. Of course, I have an index on each n column.

Right now with just the 800k records it can take over a second, sometimes multiple seconds to run the following in order to get a random row from that subset:

SELECT COUNT(1) AS total FROM table WHERE n1 = 3;
index = random number from 1 to total
SELECT data FROM table WHERE n1 = 3 LIMIT index,1;

How can I speed this up? I need it to take less than half a second if possible. Thank you.

---

Slow Execution For A Left Outer Join Query
Whats likely to be the cause of slow execution for a left outer join query?

The original query joins three tables but even if I narrow it down to one it still takes a long time to execute.

$query = "select distinct materials.* from materials";
$query .= " left outer join materials_products on materials.material_id = materials_products.material_id";

There's 914 rows in the materials table and 1348 row in the materials_products table

Is it likely to take a long time for this amount of data or is there likely to be a problem in the table(s) set up or query?

---

Is This Possible With A View?
I'm trying to cut down on the size of my database so I've changed it so that rather than implicitly setting every value in one table, I've split the data into two tables: one table (iData) contains data for the start of evey week:
iData
-----------------------------
2000-01-01 Volume: 20000000
2000-01-06 Volume: 30000000
2000-01-11 Volume: 40000000
Another table (jData) contains the delta (difference) from the previous iData entry:
jData
-----------------------------
2000-01-02 Volume: 1
2000-01-03 Volume: -2
2000-01-04 Volume: 1
2000-01-05 Volume: 3
2000-01-07 Volume: 2
2000-01-08 Volume: -5
2000-01-09 Volume: 6
2000-01-10 Volume: 1
2000-01-12 Volume: 2
2000-01-13 Volume: -4
2000-01-14 Volume: 4
2000-01-15 Volume: 3

Obviously this will save a lot of space as the delta is much smaller than storing the exact value (standard compression technique).

So what I would like to do is create a view to automatically display all of the data as if it were all stored in one table:

All Data:
---------
2000-01-01 Volume: 20000000
2000-01-02 Volume: 20000001
2000-01-03 Volume: 19999999
2000-01-04 Volume: 20000000
2000-01-05 Volume: 20000003
2000-01-06 Volume: 30000000
2000-01-07 Volume: 30000002
2000-01-08 Volume: 29999997
2000-01-09 Volume: 30000003
2000-01-10 Volume: 30000004
2000-01-11 Volume: 40000000
2000-01-12 Volume: 40000002
2000-01-13 Volume: 39999998
2000-01-14 Volume: 40000002
2000-01-15 Volume: 40000005

I know I can do this programatically, but is it possible to do this with a view? Any pointers would be much appreciated.

---

View SQL Files
I am about to by the content of a site, but it is in sql files. I need to view this content and extract to make a new site from it in html format.

How do I view SQL files and extract it content? Is there any program that does it?

---

Can I View .frm, .MYD And .MYI Files?
At my current host, the server crashed and all data seemed to be lost. Lucky for us they had had a backup of all data which they put back on the server.

But now I get errors with some tables in phpMyAdmin where they show up as "in use" and when I view them I get the following error:

MySQL
#1033 - Incorrect information in file: *.frm
or

MySQL
#1016 - Can't open file: '*.InnoDB'. (errno: 1)

Because of the issues, they send me the *.frm, *.MYD and *.MYI files.
But am I able to view these files to see the structure or data?
And can I verify if structure or data is corrupt?

---

View Implementation
Anybody has implemented View for MySQL?
I think it can be done outside of Database
if query transformation approach is used.

CREATE VIEW Model
SELECT * FROM Model_CA, Brand
WHERE Model_CA.brand_rid = Brand.rid

Select brand_name from Model
where model.name = 'Test'

This can be transformed into

SELECT brand_name FROM Model_CA, Brand
WHERE Model_CA.brand_rid = Brand.rid and
Model_CA.name = 'Test'

---

VIEW Problem
I create the next view:

CREATE VIEW VideoDB.FindCustomer AS
SELECT id, last_name&' '&name&' '&initial AS cname, picture, birthday, birthday_already, account_type, rents, exp_date, deposit, exp_deposit, auto1, kinship1, auto2, kinship2,
auto3, kinship3, auto4, kinship4, auto5, kinship5, auto6, kinship6
FROM VideoDB.customers;

When I execute the next statement the result is 0:
select cname from videodb.findcustomer;

How can I concat the whole name.
last_name&' '&name&' '&initial AS cname

---

Create A View
I have a master table and five detail tables. Now I want to have an overwiew on all detail tables that are related to the master table. The problem is that there are to many repeated records.

My wish is that to have each record of detail table in a single row with master table information in the view table. I tried to reach this with joins (left or right). No chance.. Code:

---

Mysqldump View
I try to dump the view definition and records using the following command.

mysqldump -uroot -psecret! --databases test --tables proxy_view > mytest.txt

What all I get is the view definition without records. What is the correct method of dumping a view?

---

Create View
I currently have a script that I have developed on a MS SQL server. As part of it I use a VIEW to do most of my lookups. I want to transfer it to a MYSql DB, but when I try and CREATE VIEW I get the following message....


mysql> create table t (qty INT, price INT);
mysal> insert into t values (3, 50);
mysql> create view v as select qty from t;
ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp
onds to your MySQL server version for the right syntax to use near 'view testv a
s select qty from t' at line 1
mysql>

I am using MYSQL for Netware. I am using an example that I have found to demostrate it, but it just does not work.

---

Exponential View
how to store numbers in exponential view to MySQL. E.g. 4,56e-9 = 0,00000000456

I have 4,56e-9 as a variable in perl and want to store it to MySQL. Decimal as typ seems not to work. Does i realy have to convert it to 0,00000000456?

---

How To View All The Sp Created?
is there a way to view all the stored procedure created in mysql? a command to view all avaialble sp's?

---

Creating A View
I'm running the following create view:

create view letters_of_recommendation_x_organizations as select * from organizations o left join letters_of_reccomendation l on o.OrgID=l.orgid

, and getting an error "1064 (42000): You have an error in your SQL syntax; (blah blah blah) ... syntax to use near 'view junk as select * from organizations'"

I've checked the select statement - it runs ok.

I've tried a simpler select statement as well, "create view junk as select * from organizations" .

---

CREATING A VIEW
I am trying to creat a virtual table by using a dynamic sql. This sql needs to be reuseable on different applications. To make matters more simple to explain let's say my table is a 3 column table. The ID column is a process identifier.

ID     Integer
NAME   VARCHAR
VALUE  VARCHAR

The data will appear like this:

1      FIRSTNAME      JOE
1      LASTNAME       SMITH
2      FIRSTNAME      MARY
2      LASTNAME       JONES

I need to create a table that would take the data above and create the table like below:

ID           INTEGER
FIRSTNAME    VARCHAR
LASTNAME     VARCHAR

The data would look like this:

1      JOE            SMITH
2      MARY           JONES

Any ideas on this could work?

---

View Optimization
In my database I have separate four tables and from that four tables i have created one view. Each of the table contains round five lacs of records. So when view gives result it is approximately around fifteen lacs of records,

When I call that view like "select * from MyView where name='vimal'" it takes too long to give me result. And due to this one query my application server load increases by 80%. When I execute this view that time mysql Memory consumption is approximately 80% of total memory. What I can do to optimize the same? What are the other alternatives?

---

View Implementation
Anybody has implemented View for MySQL? I think it can be done outside of Database
if query transformation approach is used.

CREATE VIEW Model
SELECT * FROM Model_CA, Brand
WHERE Model_CA.brand_rid = Brand.rid

Select brand_name from Model
where model.name = 'Test'

This can be transformed into

SELECT brand_name FROM Model_CA, Brand
WHERE Model_CA.brand_rid = Brand.rid and
Model_CA.name = 'Test'

---

Create View
I must be going mad! How come I can't create a view?

mysql> create view testview as select * from userdetails;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'view testview as select * from userdetails' at line 1

---

(View) Performance
I have a huge table with product data from different shops in my database. There is a flag column 'import' witch flagt products with 1 for my PHP script.

To select from one shop all product individually, work with it, and then update these product import flag to 0 in the database, seems to be the slowest way. I thougt about a view about all products of a shop with flag '1'. Code:

---

If Condition In View
Basically i want to add put a where condition in a view if the user variable '@org_id' is > 0. Here is some pseudo code:

CREATE VIEW view_students AS
SELECT *
FROM students
If (@org_id > 0) THEN
WHERE org_id = @org_id
END IF

I've tried lots of different approaches but i just cant figure out how to do it.

---

Distinct View
Is it possible create view(virtual table?)  from  distinct values of main tables ? Or I always must create two real tables  with foreign key ?
Example :
I have main table:

ID| Name| City
1 |  Ivan | Moscow
2 | Dmitry |  Moscow
3 | John  |  New York

Can I have view from City column with automatic update values ?

Moscow
New York

And when I add next row to main table:
4| Mario  |Rome

I want automaticaly update view :

Moscow
New York
Rome

---

Currency View
When displaying product prices in MySQL database its only showing 17.5 instead of 17.50. Tried different things in Excel to make the view display correctly, but had no luck. Any ideas?????

---

View Implementation
Anybody has implemented View for MySQL?
I think it can be done outside of Database
if query transformation approach is used.

CREATE VIEW Model
SELECT * FROM Model_CA, Brand
WHERE Model_CA.brand_rid = Brand.rid

Select brand_name from Model
where model.name = 'Test'

This can be transformed into

SELECT brand_name FROM Model_CA, Brand
WHERE Model_CA.brand_rid = Brand.rid and
Model_CA.name = 'Test'

---

View Dependencies
When creating a view, it will (almost certainly) take information from one or more existing tables/views in the database. If I come to the database with no knowledge about the view (other than its name), how can I determine which underlying tables it depends on?

(I can do this is PostgreSQL by performing queries against the pg_class & pg_depends internal tables but I haven't discovered any way of doing it in MySQL either with SQL queries or with the C API.

---

View The TimeStamp
I know you can view when a table was updated in mySQL Administrator. Is there a way in SQL to view that TimeStamp?

---

View With Parameters
can I put parameters into a view?

example

CREATE VIEW `dmz_sms`.`vwProva` AS
SELECT * FROM tb_sms where id=@ID;

I get error 1351.

---

SQL Source For A View
How do you obtain the SQL source that created a View?

I can Describe the view, but that doesn't give me the SQL that created it.

. . .

I should have searched the forums a little longer. The answer lies in the

SHOW CREATE VIEW viewName statement.

---