mysql

how to reuse subquery result in mysql

I'm doing a statistic job like this: SUM |COND1 |COND2 |... -------------------------- 100 |80 | 70 |... The SUM result is calculated from multiple tables, and the CONDs are subset of that. I wrote a sql like this: select tmp1.id,sum,cond1,cond2 from ( select id, count(*) as sum from table1 group by table1.id) tmp...

Mysql slow query: INNER JOIN + ORDER BY causes filesort

Hello! I'm trying to optimize this query: SELECT `posts`.* FROM `posts` INNER JOIN `posts_tags` ON `posts`.id = `posts_tags`.post_id WHERE (((`posts_tags`.tag_id = 1))) ORDER BY posts.created_at DESC; The size of tables is 38k rows, and 31k and mysql uses "filesort" so it gets pretty slow. I tried to use different ...

How to get a list of the columns (in order) in a database table using a Hibernate session?

Hi, I need to retrieve a list of the columns in a table in the order in which they appear. Is this possible using Hibernate? I've got a Hibernate session object, and I don't mind whether I can do it in Hibernate itself, or using JDBC. The database is MySQL, and the equivalent SQL command I want to execute is: "SHOW COLUMNS FROM " + t...

How to generate a random but unique number and display that number within the source of my image tag

Hello guys, I have done some searching but really haven't found what I'm looking for. What I would like to do is generate a random BUT unique 5 digit number and push whatever number into an img tag on my page. For example when people come to my page this number would generate and get pushed into this image tag: <img src="http://www.sa...

Mysql:How to concatenate more than one row into single row with diffrent field name?

id Subject mark Year ------------------------- 1 Maths 32 2008 1 Science 40 2009 1 Science 45 2008 1 English 50 2009 1 English 60 2008 I am looking for a result like this: id Maths Science English ----------------------------- 1 32 40 & 45 50 & 60 Please advise. I am using...

Why are my column sorts slow in Django Admin, only 1 problem table

I have a table (MySQL) with 10K+ records. Not a huge table. When working with the table in Django's Admin, the default sort order, the page loads blazing fast. Hardly noticeable. But if I attempt to sort by any column, say by ID column or even a date column, it can take over a minute to return results. Other tables work fine Any c...

Inserting records in Mysql with INSERT IGNORE and NULL values

I have a partitioned table InnoDB with several fields. I'm trying to avoid duplicates on insert. Let's say: Field1 int null Field2 int null Field3 int null Field4 int null Field5 int null I have created a UNIQUE index on those fields. I try to insert some records with NULL values and then try to reinsert them with IGNORE feature on ...

How to store characters like ♥☆ to DB?

Previous issue - was not able to store non-english characters: http://stackoverflow.com/questions/3008918/how-to-store-non-english-characters That was fixed by using UTF8. But realized today that symbols like ♥☆ are not stored correctly. They get converted to characters like ♥☆. How can this be fixed? ...

C# mysqlreader on same connection error

Hi, I must find a way to do this in C#, if possible... I must loop on my folder list (mysql table), and for each folder I instanciate I must do another query, but when I do this it says : "There is already an open DataReader associated with this Connection" and I am inside a mysqlreader loop already. Note that I have oversimplified th...

HOw do I limit array to a certain number??

actually what I am trying to do is store incoming searches in a database and then display to a web page limiting to a certain number say 10 with the heading of "Recent Searches". if you LIMIT 10 in database query then It stops after 10. so thought will cut off loop but both case it stops at 10. so after that what ever searches happens it...

Slow MySQL query....only sometimes

I have a query that's used in a reporting system of ours that sometimes runs quicker than a second, and other times takes 1 to 10 minutes to run. Here's the entry from the slow query log: # Query_time: 543 Lock_time: 0 Rows_sent: 0 Rows_examined: 124948974 use statsdb; SELECT count(distinct Visits.visitorid) as 'uniques' FROM Visit...

mysql and trigger usage question

I have a situation in which I don't want inserts to take place (the transaction should rollback) if a certain condition is met. I could write this logic in the application code, but say for some reason, it has to be written in MySQL itself (say clients written in different languages will be inserting into this MySQL InnoDB table) [that's...

How do I efficiently locate key-value pairs in a multi-dimensional PHP array?

I have an array in PHP as a result of the following query to a Wordpress database: SELECT * FROM wp_postmeta WHERE post_id = :id I am returned a multidimensional array that looks like this: Array ( [0] => Array ( [meta_id] => 380 [post_id] => 72 [meta_key] => _edit_last [meta_value] => 1 ) ... etc. What is the best way to fi...

MySQL - Limit a left join to the first date-time that occurs?

Simplified table structure (the tables can't be merged at this time): TableA: dts_received (datetime) dts_completed (datetime) task_a (varchar) TableB: dts_started (datetime) task_b (varchar) What I would like to do is determine how long a task took to complete. The join parameter would be something like ON task_a = task_b AN...

How to combine two rows and calculate the time difference between two timestamp values in MySQL?

I have a situation that I'm sure is quite common and it's really bothering me that I can't figure out how to do it or what to search for to find a relevant example/solution. I'm relatively new to MySQL (have been using MSSQL and PostgreSQL earlier) and every approach I can think of is blocked by some feature lacking in MySQL. I have a "...

mysqli_query SELECT FROM WHERE [ partial match ] ?

Hi guys, I have a database that a time filed that is stored in this format "2010.06.04. | 18:53 01". What I need is to select rows that have a specific date, for example "2010.06.04." Right now I am doing it my first selecting all rows and then looping through them and adding ones with the specified date to a new array. Maybe there is ...

Using views as a data interface between modules in a database

Hello, I am working on the database layout of a straighforward small database in Mysql. We want to modularize this system in order to have more flexiblity for different implementations we are going to make. Now, the idea was to have one module in the database (simple a group of tables with constraints between them) pass its data to the ...

is mysql index useful on column 'state' when only doing bit-operations on the column?

I have a lot of domain entities (stored in mysql) which undergo lots of different operations. Each operation is executed from a different program. I need to keep (flow)-state for these entities which I implemented in as a long field 'flowstate' used as a bitset. to query mysql for entities which have undergone a certain operation I do s...

SELECT an arbitrarily deep tree that is represented by the adjacency list model in mysql?

In mysql, I have a tree that is represented using the adjacency list model. MYTREE id parent_id title I am wondering: Given the id of a node, is there any way to SELECT the entire tree beneathe that node, complete with depth information? The tree is arbitrarily deep, so I cannot say how many levels there may be. But the re...

java.sql.SQLException: Parameter number X is not an OUT parameter

Hi guys, I'm struggling with getting the result OUT variable from a MySQL stored procedure. I get the following error: java.sql.SQLException: Parameter number 3 is not an OUT parameter The stored procedure looks like this: CREATE DEFINER=`cv_admin`@`localhost` PROCEDURE `CheckGameEligibility`( IN gID INT(10), IN uID INT(10), ...