-
happy b-day!
Date: 08/14/19 Keywords: no keywords
Congrats to
frantsouzov ! :) -
Efficient full-text searchs on large sets of data
Date: 10/15/10 Keywords: mysql, database, sql
A database application I've written uses a table with around 600,000 rows. Each row has a text field 500-5000 characters long. I periodically need to find all the rows containing a particular phone number, name, or address, ie.'123-4567', 'john smith', '1950 Main St N'
I'm doing this using
SELECT * FROM `tb_archive` WHERE `text` LIKE '%john smith%' ORDER BY `date` DESC
The problem is that it is too slow. Most searches take 30-60 seconds. If multiple searches are done the server response begins to slow to a crawl for other users.
I've looked at mysql built in full-text indexing - but I'm not sure if it can work since I only need exact matches, don't care about relavence, and often search for numbers and short words.
Any suggestions as to how I can do this more efficiently? -
Efficient full-text searchs on large sets of data
Date: 10/15/10 Keywords: mysql, database, sql
A database application I've written uses a table with around 600,000 rows. Each row has a text field 500-5000 characters long. I periodically need to find all the rows containing a particular phone number, name, or address, ie.'123-4567', 'john smith', '1950 Main St N'
I'm doing this using
SELECT * FROM `tb_archive` WHERE `text` LIKE '%john smith%' ORDER BY `date` DESC
The problem is that it is too slow. Most searches take 30-60 seconds. If multiple searches are done the server response begins to slow to a crawl for other users.
I've looked at mysql built in full-text indexing - but I'm not sure if it can work since I only need exact matches, don't care about relavence, and often search for numbers and short words.
Any suggestions as to how I can do this more efficiently? -
A question of logic
Date: 03/02/10 Keywords: php
I've struggled with this for a few days now and thought some of you may have insight.
I need to display four columns of data from this table:
review_id
member_id
question_id
answer_id
(Review_id is a set of questions given to a member. Each colum represents questions for a given member, from a specific review_id. The idea is to compare review answers between 4 members)
Displaying a single member's results is easy enough to query. But where I am stuck is showing multiple member question/answers on the same screen, side by side. I'm tempted to nest queries with PHP. But maybe there is a better way?
I cannot change the layout of the page -- must have four columns, with answers listed on each row.
Thoughts? -
A question of logic
Date: 03/02/10 Keywords: php
I've struggled with this for a few days now and thought some of you may have insight.
I need to display four columns of data from this table:
review_id
member_id
question_id
answer_id
(Review_id is a set of questions given to a member. Each colum represents questions for a given member, from a specific review_id. The idea is to compare review answers between 4 members)
Displaying a single member's results is easy enough to query. But where I am stuck is showing multiple member question/answers on the same screen, side by side. I'm tempted to nest queries with PHP. But maybe there is a better way?
I cannot change the layout of the page -- must have four columns, with answers listed on each row.
Thoughts? -
Database in SVN
Date: 10/09/09 Keywords: mysql, database, sql
Anyone know how to store a MySQL database schema (and maybe data from some tables hopefully based on table structure) into SVN?
-
Database in SVN
Date: 10/09/09 Keywords: mysql, database, sql
Anyone know how to store a MySQL database schema (and maybe data from some tables hopefully based on table structure) into SVN?
-
Effective coding
Date: 09/22/09 Keywords: mysql, database, sql
I found an instruction set that said to list the first, and last name of all employees that had neither 'SON' nor 'DAUGHTER' listed in their dependency files. I came up with a query that gave me the results I wanted by assuming the database only takes SPOUSE, SON, and DAUGHTER, but assuming has always caused me trouble, so I would like to know: Is there any better way I could have gone about approaching this so it's not going by a general assumption of dep_relation = 1, and so forth?
mysql> SELECT * FROM dependent; +-------------+------------+------------+-------------------+------------------+ | dep_emp_ssn | dep_name | dep_gender | dep_date_of_birth | dep_relationship | +-------------+------------+------------+-------------------+------------------+ | 999444444 | Jo Ellen | F | 1996-04-05 | DAUGHTER | | 999444444 | Andrew | M | 1998-10-25 | SON | | 999444444 | Susan | F | 1975-05-03 | SPOUSE | | 999555555 | Allen | M | 1968-02-29 | SPOUSE | | 999111111 | Jeffery | M | 1978-01-01 | SON | | 999111111 | Deanna | F | 1978-12-31 | DAUGHTER | | 999111111 | Mary Ellen | F | 1957-05-05 | SPOUSE | +-------------+------------+------------+-------------------+------------------+ mysql> SELECT * FROM employee; +-----------+---------------+----------------+ | emp_ssn | emp_last_name | emp_first_name | +-----------+---------------+----------------+ | 999666666 | Bordoloi | Bijoy | | 999555555 | Joyner | Suzanne | | 999444444 | Zhu | Waiman | | 999887777 | Markis | Marcia | | 999222222 | Amin | Hyder | | 999111111 | Bock | Douglas | | 999333333 | Joshi | Dinesh | | 999888888 | Prescott | Sherri | +-----------+---------------+----------------+ What I generated was: SELECT emp_first_name, emp_last_name FROM ( SELECT emp_first_name, emp_last_name, dep_relationship, COUNT(dep_relationship) AS count FROM dependent JOIN employee ON dep_emp_ssn = emp_ssn GROUP BY emp_ssn ) tempTable WHERE tempTable.count = 1 AND tempTable.dep_relationship != 'SON' AND tempTable.dep_relationship != 'DAUGHTER'; To get: +----------------+---------------+ | emp_first_name | emp_last_name | +----------------+---------------+ | Suzanne | Joyner | +----------------+---------------+
So really, I covered the "Son nor Daughter" case, but it's the count = 1 that I feel could be "bettered" so to speak.
I appreciate the feedback. ;] -
Effective coding
Date: 09/22/09 Keywords: mysql, database, sql
I found an instruction set that said to list the first, and last name of all employees that had neither 'SON' nor 'DAUGHTER' listed in their dependency files. I came up with a query that gave me the results I wanted by assuming the database only takes SPOUSE, SON, and DAUGHTER, but assuming has always caused me trouble, so I would like to know: Is there any better way I could have gone about approaching this so it's not going by a general assumption of dep_relation = 1, and so forth?
mysql> SELECT * FROM dependent; +-------------+------------+------------+-------------------+------------------+ | dep_emp_ssn | dep_name | dep_gender | dep_date_of_birth | dep_relationship | +-------------+------------+------------+-------------------+------------------+ | 999444444 | Jo Ellen | F | 1996-04-05 | DAUGHTER | | 999444444 | Andrew | M | 1998-10-25 | SON | | 999444444 | Susan | F | 1975-05-03 | SPOUSE | | 999555555 | Allen | M | 1968-02-29 | SPOUSE | | 999111111 | Jeffery | M | 1978-01-01 | SON | | 999111111 | Deanna | F | 1978-12-31 | DAUGHTER | | 999111111 | Mary Ellen | F | 1957-05-05 | SPOUSE | +-------------+------------+------------+-------------------+------------------+ mysql> SELECT * FROM employee; +-----------+---------------+----------------+ | emp_ssn | emp_last_name | emp_first_name | +-----------+---------------+----------------+ | 999666666 | Bordoloi | Bijoy | | 999555555 | Joyner | Suzanne | | 999444444 | Zhu | Waiman | | 999887777 | Markis | Marcia | | 999222222 | Amin | Hyder | | 999111111 | Bock | Douglas | | 999333333 | Joshi | Dinesh | | 999888888 | Prescott | Sherri | +-----------+---------------+----------------+ What I generated was: SELECT emp_first_name, emp_last_name FROM ( SELECT emp_first_name, emp_last_name, dep_relationship, COUNT(dep_relationship) AS count FROM dependent JOIN employee ON dep_emp_ssn = emp_ssn GROUP BY emp_ssn ) tempTable WHERE tempTable.count = 1 AND tempTable.dep_relationship != 'SON' AND tempTable.dep_relationship != 'DAUGHTER'; To get: +----------------+---------------+ | emp_first_name | emp_last_name | +----------------+---------------+ | Suzanne | Joyner | +----------------+---------------+
So really, I covered the "Son nor Daughter" case, but it's the count = 1 that I feel could be "bettered" so to speak.
I appreciate the feedback. ;] -
sSIMPLE FUCKING PROBLEM
Date: 09/01/09 Keywords: no keywords
i have this thing on another page it works but its like the same and doesnt work on the page im working on. this is what it is:
SELECT column, column2 FROM table
doesnt work. only shows column one. ive done all the variations i can think of SELECT `column1`, `column2` ectttt fucking ect does anyone know why this is o pathetic and will NOT WORK? -
sSIMPLE FUCKING PROBLEM
Date: 09/01/09 Keywords: no keywords
i have this thing on another page it works but its like the same and doesnt work on the page im working on. this is what it is:
SELECT column, column2 FROM table
doesnt work. only shows column one. ive done all the variations i can think of SELECT `column1`, `column2` ectttt fucking ect does anyone know why this is o pathetic and will NOT WORK? -
MySQL
Date: 08/07/09 Keywords: mysql, database, sql
I am not entirely sure if this is the right place to ask but I thought to give it a shot anyway.
I want to create a new MySQL Database so I downloaded MySQL 4.1.22 for Windows and installed it. After installing it I have the program MySQL Command Line Client and MySQL Server Instance Config Wizard. For both of them I need a root user name and a root password but how do I know those, where can I find them?
i hope you can help me. -
MySQL
Date: 08/07/09 Keywords: mysql, database, sql
I am not entirely sure if this is the right place to ask but I thought to give it a shot anyway.
I want to create a new MySQL Database so I downloaded MySQL 4.1.22 for Windows and installed it. After installing it I have the program MySQL Command Line Client and MySQL Server Instance Config Wizard. For both of them I need a root user name and a root password but how do I know those, where can I find them?
i hope you can help me. -
oracle blob to mysql blob *update*
Date: 07/20/09 Keywords: php, mysql, xml, database, sql
hey again
i'm trying to convert an oracle db to mysql. so far everything is going ok except for the files in the db. the old admin was storing jpg, pdf, doc, xml files and whatever else you can think of into a blob field in the table on oracle.
is this the best way to do things? i mean sure all you need to do is back up the database and presto you have all the files. but i'm having a hell of a time getting a backup. 440 entries in the table comes up to a 180 megabyte text file. phpmyadmin won't process the file because it times out, sqlyog and navicat are having trouble with the file size saying they're running out of memory.
the closest i've come to completing this transfer is using toad for oracle and outputting the table to an mdb file. then in navicat i use the wizard and import the mdb, it sees the fields of the table perfectly, but the blobs come out having 0k. aside from that small issue i keep getting error 2006 when importing. and lose between 3 and 219 entries depending on the time i'm importing.
so, at a loss, am i doing things right? is it possible to convert oracle blobs to mysql blobs? personally if i had written the original site i would just save a link to the actual file in the db and not store the actual file in the db. does that make sense?
*UPDATE*
so i tried using the migration tool, no luck. i'm missing libraries and my tech guy wasn't here yesterday. the site for the oracle libraries didn't want to work either.
so this is what i ended up doing:
in toad i saved a csv/txt file of the table without the blobs in the csv. cut the size down to 88k.
i uploaded that via sqlyog into the db. no problems.
then in toad i saved the blobs themselves as individual .dat files. so at least i have the files this way.
the db actually has the filename in it, so it was just a simple extracting the filename, copying the .dat files from one folder to another on the server with a simple php script and changing the names to reflect their actual names. all the files can be opened no problem once the name have been changed.
now i just need to link to said files from db to directory on the server.
thanks again for the input :) -
oracle blob to mysql blob *update*
Date: 07/20/09 Keywords: php, mysql, xml, database, sql
hey again
i'm trying to convert an oracle db to mysql. so far everything is going ok except for the files in the db. the old admin was storing jpg, pdf, doc, xml files and whatever else you can think of into a blob field in the table on oracle.
is this the best way to do things? i mean sure all you need to do is back up the database and presto you have all the files. but i'm having a hell of a time getting a backup. 440 entries in the table comes up to a 180 megabyte text file. phpmyadmin won't process the file because it times out, sqlyog and navicat are having trouble with the file size saying they're running out of memory.
the closest i've come to completing this transfer is using toad for oracle and outputting the table to an mdb file. then in navicat i use the wizard and import the mdb, it sees the fields of the table perfectly, but the blobs come out having 0k. aside from that small issue i keep getting error 2006 when importing. and lose between 3 and 219 entries depending on the time i'm importing.
so, at a loss, am i doing things right? is it possible to convert oracle blobs to mysql blobs? personally if i had written the original site i would just save a link to the actual file in the db and not store the actual file in the db. does that make sense?
*UPDATE*
so i tried using the migration tool, no luck. i'm missing libraries and my tech guy wasn't here yesterday. the site for the oracle libraries didn't want to work either.
so this is what i ended up doing:
in toad i saved a csv/txt file of the table without the blobs in the csv. cut the size down to 88k.
i uploaded that via sqlyog into the db. no problems.
then in toad i saved the blobs themselves as individual .dat files. so at least i have the files this way.
the db actually has the filename in it, so it was just a simple extracting the filename, copying the .dat files from one folder to another on the server with a simple php script and changing the names to reflect their actual names. all the files can be opened no problem once the name have been changed.
now i just need to link to said files from db to directory on the server.
thanks again for the input :) -
Searching and counting
Date: 07/14/09 Keywords: mysql, sql
I have some difficulties in building a right query for a situation. A MySQL Table 'patients' looks like this
Name Doctor Date
-------------------------------
John Deere Dr.Watson 2009-02-11
Bill Fairy Dr.Dylan 2009-03-03
John Deere Dr.Dylan 2009-05-19
I'm trying to count records with same Name field AND all other fields in reply must contain appropriate field in LAST record (based in Date field). I'm doing following query:
SELECT *, COUNT(*) AS num FROM patients GROUP BY name, HAVING COUNT(*)>1 ORDER BY date DESC;
I think that ORDER BY DESC helps me to get in reply fields of last record. But in reply I always receive data contained in my oldest record :( I stumbled :) Anyone can help?
Thanks in advance. -
Searching and counting
Date: 07/14/09 Keywords: mysql, sql
I have some difficulties in building a right query for a situation. A MySQL Table 'patients' looks like this
Name Doctor Date
-------------------------------
John Deere Dr.Watson 2009-02-11
Bill Fairy Dr.Dylan 2009-03-03
John Deere Dr.Dylan 2009-05-19
I'm trying to count records with same Name field AND all other fields in reply must contain appropriate field in LAST record (based in Date field). I'm doing following query:
SELECT *, COUNT(*) AS num FROM patients GROUP BY name, HAVING COUNT(*)>1 ORDER BY date DESC;
I think that ORDER BY DESC helps me to get in reply fields of last record. But in reply I always receive data contained in my oldest record :( I stumbled :) Anyone can help?
Thanks in advance. -
selecting part of the data in a field
Date: 07/12/09 Keywords: no keywords
i have a field with a full street address, something like "1234 main st." is it possible to select just the numerical part of the data?
i ask because i need to compare that to another field. so it'd be real easy to just do something like
select * from `table` where (MAGIC, `address`) = `other_field` -
selecting part of the data in a field
Date: 07/12/09 Keywords: no keywords
i have a field with a full street address, something like "1234 main st." is it possible to select just the numerical part of the data?
i ask because i need to compare that to another field. so it'd be real easy to just do something like
select * from `table` where (MAGIC, `address`) = `other_field` -
How to Merge Databases?
Date: 07/06/09 Keywords: no keywords
I have two tables (same DB format) that I'd like to merge.
One table has the format of several colums:
ID, AlphaCol, BetaCol, GammaCol
The other has different columns:
ID, Foo, Bar, Baz
The ID colum in both are what ties the separate tables together.
I started out the process with the following command:
create table combinedtable select * from firsttable;
This created combinedtable with the columns ID, AlphaCol, BetaCol, GammaCol. Everything's cool so far.
Then, I thought I could do a command like:
insert into combinedtable select secondtable.Foo, secondtable.Bar, secondtable.Baz from secondtable, combinedtable where combinedtable.ID = secondtable.ID;
When I try this, however, I consistently get the error:
Error 1136 (21S01): Column doesn't match value count at row 1.
So, I'm missing something basic, but I didn't think this would be that difficult. What am I doing wrong?
| || | Next page |