mysql

MySQL: How to perform optimization on all tables with a stored procedure?

I need to perform check table, optimize table and analyze table statements on all tables in a database. And I want to do it inside a stored procedure. Is it possible? I can't figure out how to iterate through the table names and then use those names to perform the 3 statements. ...

Timestamp as int field, query performance

Hello, I'm storing timestamp as int field. And on large table it takes too long to get rows inserted at date because I'm using mysql function FROM_UNIXTIME. SELECT * FROM table WHERE FROM_UNIXTIME(timestamp_field, '%Y-%m-%d') = '2010-04-04' Is there any ways to speed this query? Maybe I should use query for rows using timestamp_field...

How do you export tables from phpMyAdmin as plain text table format?

I've been through most of the export formats and can't seem to find it. I'd like to be able to export tables visually like in this answer: id | name | name ---------------- 1 | One | Partridge 2 | Two | Turtle Doves 3 | Three| French Hens 4 | Four | NULL ...

highlighting search results in php/mysql

how do i highlight search results from mysql query using php? this is my [modified] code: $search_result = ""; $search_result = $_GET["q"]; $result = mysql_query('SELECT cQuotes, vAuthor, cArabic, vReference FROM thquotes WHERE cQuotes LIKE "%' . $search_result .'%" ORDER BY idQuotes DESC', $conn) or die ('Error: '.mysql_error()); ...

MySQL GUI Programs

Are there any programs with a gui which let you create a MySQL database, create tables, define the primary and foreign keys and insert data? I am tired of having to use the mysql command line client, very tedious. ...

How to set a limit to inner query in Hibernate?

I have HQL like this: from Table1 t1 where t1.name not in (select t2.name from Table2 t2 order by t2.date limit 10) The problem is it doesn't understand limit keyword. Is there a way to run such query without splitting it into two subqueries? ...

same logged in user assigned different session ids (in different browsers)

Hi, I've implemented a mysql-based session interface in php. I just found out that if I log in to my account using browser A (e.g. Chrome), and then I log in to the same account in another browser B (e.g. IE), each browser is assigned 2 separate session ids. How can I make it such that when I log in again using browser B, I retain the...

MySQL Procedures : Standard/Best Place For Code Documentation?

I'd like to put a paragraph or so for documentation on each of my MySQL procedures: date, author, purpose, etc. Is there a standard, accepted, or best place to put this kind of documentation? For example, In MSSQL, some IDE's will generate a template for you to start coding your procedure; it comes with a little place for code documenta...

PHP Strange error "0:". Possible Object syntax problem?

I'm trying to transfer a site from one hosting company to another. I was using 000webhost until the client had hosting and a domain. Now that the client has their domain and hosting, using FatCow.com, I can not for the life of me, debug my PHP code. I'm not getting any errors. I have a successful DB connection. If you procedurally displa...

How to select the first row for each group in MySQL?

In C# it would be like this: table .GroupBy(row => row.SomeColumn) .Select(group => group .OrderBy(row => row.AnotherColumn) .First() ) Linq-To-Sql translates it to the following T-SQL code: SELECT [t3].[AnotherColumn], [t3].[SomeColumn] FROM ( SELECT [t0].[SomeColumn] FROM [Table] AS [t0] GROUP BY ...

Query for props list with or without values

Hi, I'm trying to make a SELECT on three relational tables like these ones: table_materials -> material_id - material_name table_props -> prop_id - prop_name table_materials_props - row_id -> material_id -> prop_id - prop_value On my page, I'd like to get a result like this one but i have some problem with the query: material pr...

How do I reorder columns in MySQL Query Editor?

I want to move column OtherSupport below Amount2 ... is there an easy way to do this? ...

In SQL / MySQL, are there reasons not to put one-to-one relationship in the same table?

One-to-one relationship could usually be stored in the same table. Are there reasons not to store them in the same table? ...

Mysql regexp performance question

Rumour has it that this; SELECT * FROM lineage_string where lineage like '%179%' and lineage regexp '(^|/)179(/|$)' Would be faster than this; SELECT * FROM lineage_string where lineage regexp '(^|/)179(/|$)' Can anyone confirm ? Or know a decent way to test the speed of such queries. Thanks ...

Cannot connect to local database.

Hi, I have apache and mysql set up on my local machine (Mac). Whenever I go to any of my local sites I get the "Error establishing a database connection" whenever anything mySql related happens. It was working perfectly yesterday, but when I started my computer today it is not. I can login to mysql on the terminal and that all works...

Rolling back file moves, folder deletes and mysql queries

This has been bugging me all day and there is no end in sight. When the user of my php application adds a new update and something goes wrong, I need to be able to undo a complex batch of mixed commands. They can be mysql update and insert queries, file deletes and folder renaming and creations. I can track the status of all insert comm...

Is there a way to LIMIT results per group of result rows in MySQL?

Hi, I have the following query: SELECT title, karma, DATE(date_uploaded) as d FROM image ORDER BY d DESC, karma DESC This will give me a list of image records, first sorted by newest day, and then by most karma. There is just one thing missing: I want to only get the x images with the highest karma per day. So for example, per day I...

How many millions records can Mysql reasonably deal with with .NET ?

Anybody can tell his experience with MySQL and .NET dealing with millions of records ? Would myself need to be able to cope with 50 to 100 Millions records and would like to hear about how you cope with this in your precise context so that I may inspire from it. ...

Mysql: cannot call decode in stored procedure

hello. when create any stored procedure the function decode dont work delimiter $$ create procedure guardausuario( varKusuario varchar(8), varNombre text, varPwd BLOB) begin declare varValor varchar(128); select encode(varPwd,varKusuario) into varValor; insert into usuarios (k_usuario, nombre, pwd) values (varKusuario,varNombre,v...

mysql query the latest date

I am running this query SELECT sh.*, u.initials AS initals FROM database1.table1 AS sh JOIN database2.user AS u ON u.userID = sh.userid WHERE id = 123456 AND dts = ( SELECT MAX(dts) from database1.table1 ) ORDER BY sort_by, category In the table1 I have records like this dts status ...