How To Optimize Mysql Like '%keyword%'

Jul 22, 2005

It seems index does not work for like '%keyword%'. I have a search query on a very large table:

SELECT id FROM table WHERE name like '%user_keyword%' AND number1 =1 AND number2= 0 AND number3=3 ORDER BY somthing.

The problem is the table is very large and I can't image a search without index. How to preform/optimize this query?

ADVERTISEMENT

Match Both A Htmlentified Keyword And Non-htmlentified Keyword At The Same Time In A Fulltext MySQL Search

Jul 15, 2011

I've been learned to keep my data in MySQL as raw as possible. Here's an example of how I store content in my MySQL database:

title (VARCHAR, 255) =>
content (TEXT) => <p>A paragraph about r&eacute;f&eacute;rences...</p>

When I output it to a page, I use htmlentities() on title, but ofcourse not on content. I feel this is the correct way of storing it, since title stores only text and content stores HTML.However, I now see a limitation to this: when I'm do a fulltext search to match a specific keyword (such as , I need to search both for AND r&eacute;f&eacute;rences in order to retrieve all results.

[code]....

View 2 Replies View Related

Optimize In MySQL

Jul 12, 2005

how to work out the size (in KB or similar) of a database. In one of my scripts I use the Optimize command in MySQL as there is a lot of data added and removed from the database. A quick trawl through Google and MySQL.com as well as browsing through PHPMyAdmin code has left me without a clue.

View 2 Replies View Related

Optimize The MySQL Table?

Jun 5, 2010

This is for an upcoming project. I have two tables - first one keeps tracks of photos, and the second one keeps track of the photo's rank

Photos:
+-------+-----------+------------------+
| id | photo | current_rank |
[code]...

That means some extra code in PHP to split the rank/time (and sorting), but that looks OK to me. Is this a correct way to optimize the table for performance?

View 9 Replies View Related

Optimize Or Combine These MySQL Queries?

Sep 16, 2009

I want to check for the username in the users table. If it's found, then I want to get the id. Otherwise, I want to insert the username as a new record, then get the id.
Here's my code:

<?PHP
$sql = "SELECT id FROM users where username = '$username'";
$query = mysql_query($sql) or die(mysql_error());
$result = mysql_num_rows($query);
if($result){
code....

How can I optimize or combine these MySQL queries?

Can I select and insert in the same query?

and would it be better and faster?

View 2 Replies View Related

How To Optimize Mysql Full Match

Dec 22, 2009

do you know some trick about how can I optimize more a full match search?

The code that I'm trying to optimize:

$do = $this->select()
->where('MATCH(`name`,`ort`) AGAINST( ? IN BOOLEAN MODE)', $theString)
->order('premium DESC');

The search should search for some companies...and let's say that in the field name I have: Google and in the ort field I have new york. I want to do a query like: google center in new york and to give me as a result all the google centers from new york only!

View 3 Replies View Related

How To Optimize Mysql Database Automatically

Jun 16, 2010

i have a database and need to automatically optimize this 24 hourly

What should I do?

View 2 Replies View Related

Optimize MYSQL (3) Table Join Query?

Jan 31, 2010

Running an EXPLAIN on some of my query tests have resulted in slow ALL joins even with indexes. How do I make a MYSQL query with the following information more efficient?

Tables

counter: id (pk), timestamp, user_id (fk)
user: id (PK), username, website_id (fk)
website: id (pk), sitename
SELECT t2.username, t3.sitename, count(*) as views FROM counter t1
LEFT JOIN user t2 ON t2.id = t1.user_id
LEFT JOIN website t3 ON t3.id = t2.website_id
WHERE t1.id <> ""
GROUP BY t1.id
ORDER BY t1.id DESC

The result in an html table:

username, sitename, counter_views

View 3 Replies View Related

Optimize Mysql Fulltext Union Search?

Apr 19, 2011

Making a mysql fulltext search.my database table article1 has ~18000 articles, article2 has ~7000 articles, article3 has ~13000 articles.FIELD cat is a INDEX field.Now I want to make a union search.there are 5 groups words put into 3 table, match out the results.But the process time is 3.1213495136 seconds.(I add microtime()to see how much time it will cost).Is there any way to optimize mysql fulltext union search?

(SELECT title,content,date FROM article1 WHERE (cat='novel' AND MATCH (title,content) AGAINST ('+Mary +Barnard' IN BOOLEAN MODE))OR (cat='novel' AND MATCH (title,content) AGAINST ('+Patricia +Beer' IN BOOLEAN MODE))

[code]...

View 2 Replies View Related

Do A Search By Keyword, And Show The Results Highlighting The Keyword.

Nov 4, 2005

I made a search engine, and now I'd like to do a search by keyword, and show the results highlighting the keyword. I have this: PHP Code:

$string = "This is a test, but this will be...";
$keyword = "this";
$new_string = eregi_replace($keyword, "<strong>$keyword</strong>", $string );

View 3 Replies View Related

Keyword Density Calculation On A Keyword Phrase?

Feb 13, 2011

When attempting to calculate the keyword density of a single keyword in a string of content, the formula is pretty straightforward: kwd = (keyword count / total word count ) * 100However, what should the formula be when we are looking for keyword density of a keyword phrase?For example, how would you calculate the keyword density of the phrase "blue widgets" in the following string?

$myContent = "Blue widgets in a field
of widgets blue makes for lots of widgets, true. But
if a widget is blue, is it still a
"blue widget" or just a lone widget in a sea
of blue?";
[code]....

View 3 Replies View Related

Search Keyword With Mysql?

May 24, 2011

What should I use, to search for a keyword with mysql ? I have a word and in the query I have

wordless
something word something
someword some
else
[code].....

View 2 Replies View Related

Misspelled Keyword Matching In Mysql

Dec 2, 2006

I have few keyword lists already in mysql table. for example keywords are like indian army, indian boys, USA people and something like that.

So if someone searches using my search and use some misspelled keyword like he enters "indan boys" so i want that "Indian Boys" option should be selected from my sql database.

Is there anyway by which i can display nearest correct keyword from database?

View 1 Replies View Related

Multiple Keyword Search In Mysql?

Feb 3, 2010

Here's what I'm trying to do: I have a database (directors) containing 8 columns. Each column contains sentences or keywords.I have built a search form (multiple words separated by a space) and wrote a mysql statement that cycles through:1. the array of keywords2. the array of columnsI'd like the results to be based on ALL of the search words but for some reason, my statement shows results that include any of the keywords.What is it that I'm doing wrong?this is the coding I have used so far without success:

if ($searching2 =="yes")
{
echo "<h2>Results</h2><p>";
if ($SearchWords == "")
[code].....

View 1 Replies View Related

OR Keyword Be Use For Same Field In A Mysql Table?

May 24, 2011

I'm trying to create a search form where members of a site can search other members whose user information is stored in a mysql database. One of the search parameters is gender which searches for records from a column in the database called gender where members can either have the value "man" or "woman". The user can choose between 3 options from a pull down menu named gender (man, woman, both). Then I'm thinking about using a select query to search the database upon submission of the form, which goes something like this:

[syntax=php]
$sql= SELECT* FROM members WHERE gender ='{$_POST['gender']}' ;
[/syntax]
[code]....

View 6 Replies View Related

Mysql Search Multiple Tables Using A Keyword?

Jul 4, 2011

I have three tables in my database which are:

messages
topics
comments

Each of these tables has two fields called 'content' and 'title'. I want to be able to use 'Like' in my sql statement to look at 'messages.content', 'messages.title', 'topics.content', 'topics.title', 'comments.content' and 'comments.title' using a keyword.

So far, my query is able to find results from only one table:

mysql_query("SELECT * FROM messages
WHERE content LIKE '%" . $keyword . "%'
OR title LIKE '%" . $keyword ."%'");

I am also wondering, once I get the results from multiple tables, how can I tell what result is from what table?

View 3 Replies View Related

Search More Than 1 Field In Mysql Database For A Keyword?

Apr 28, 2010

Does anyone know how i can make a query for a keyword (say apple) in different field?Example would be...I have 4 fields in my databasefield 1, field2 field 3, field 4each field has different keywords, apple, banana, orange, strawberryWhat i want to do is make a query with php were it searches for say apple, but it searches all 4 fields to see in which field apple is atDoes that make sense?Could i do the following...

Code: [Select]<?php
$sql = "SELECT * FROM table WHERE field2='1' || field2='1'";
?>

View 1 Replies View Related

MySQL: CONCAT_WS(' ', Name_first, Name_middle, Name_last) Like '%keyword%'?

Jun 14, 2010

I'm setting up a keyword search across multiple fields: name_first, name_middle, name_last but I'm not getting the results I'd like. Here's the query:

"SELECT accounts_users.user_ID, users.name_first, users.name_middle, users.name_last, users.company
FROM accounts_users, users
WHERE accounts_users.account_ID = '$account_ID' AND accounts_users.user_ID = users.id AND CONCAT_WS(' ', users.name_first, users.name_middle, users.name_last) LIKE '$user_keyword%'
ORDER BY users.name_first ASC"

So, if I've got three names in the DB:Aaron J Ban

View 1 Replies View Related

Optimize Mysql Query If Have Too Many OR Operators In Query?

Aug 13, 2009

using PHP and MySQLi have grabbed an array of facebook user ids from facebook.Now i want to find the corresponding username in my application for this array.learly in my application the user table contains unique username and unique fb_uid values.my rudimentary understanding oof programming led me to 2 ways:1) us a loop and run through the array of fb_uid and find the username one by one.OR2) create a monster query like select distinct(username) from users where fb_uid = value1 OR fb_uid = value2 ...

View 3 Replies View Related

File Only Search 'Begin With Keyword' - Make It Search 'Contain Keyword'?

Jan 22, 2011

This search.php file only search 'Begin with keyword' only, how to make it search 'Contain keyword'

Quote: for example there already a name in database, lets say James

1. right now if user search for 'Begin with keyword' like Ja, or Jam, it will showing it (James, and other name begin with that keyword)

2. but not if search for 'End with keyword' or 'Contain keyword', like mes, or ames, or ame (it will not showing James, and other name with that keyword in the middle or in the end)

i need to make it like no.2 above, can search for 'Contain keyword', not search for 'Begin with keyword' only.

$sq = "select * from names where $gn $ori and (name like '$l%' OR meaning like '$l%') order by name";
$sql = "Select * from names where $gn $ori and (name like '$l%' OR meaning like '$l%')

order by name limit $limitvalue,$limit"; * = are the keyword that typed by user names = the database table contain name list meaning = the database table contain name meaning list

[Code].....

View 1 Replies View Related

Is There A Way To Optimize My Program?

Apr 8, 2005

My Program is about parsing website through their url, My program is working properly but everytime it enters URL with so many sub URL (i mean thousand) my program seem to stop responding. Can you suggest something to this problem. somebody told me that i use the sleep() fucntion to not force my script to run successively, to give a pause to my program. Any thing more you can suggest? i'm not after the speed of execution but what im after is that he finish his job no matter how much time it takes....

View 1 Replies View Related

Sql Optimize Help Needed

Mar 13, 2004

SELECT * FROM post WHERE forumid=1 ORDER BY ctime LIMIT x,10

ctime=creation time
both forumid and ctime is index separately
it take too much time when x is so large.
how can i optimize this query?

View 1 Replies View Related

How To Optimize A PHP Forum?

Jul 9, 2006

I want to know, how to optimize a forum made of PHP. What about URL re-writing. I have read that Long Dynamic URL are not supported by Search Engines and these pages are hard to crawl. I have read about converting Dynamic URL to static one (the one which SEarch Engines prefers)..but i dont know how to do that?

View 1 Replies View Related

Optimize: In Query Or Php

Sep 18, 2006

I have a db table with 40 fields, any of which can be &#390;' (not null). I want to select all rows that don't have a single &#390;' value in any field. Writing my query, I couldn't find a simple way to do this. So I'll select '*' and make a php loop to weed out rows with &#390;' values. Only there must be a better way using SQL I imagine.

View 1 Replies View Related

How To Optimize Mysql_query

Mar 11, 2010

I have always wondered this, I need a way to loop some mysql_query's.For example, I have a query in the header that will grab the favorite games of a member. Though, to display this information, I thought it would be fine to put a mysql_query inside the while loop to get that specific game's information.

Is that a bad way to do things? If so, how is a better way to grab that information. I think it is a bad way to do it because half the time those second query's don't even run.

View 12 Replies View Related

How To Optimize Server Resources

Apr 19, 2003

i have done script that save IP'addresses, but got into question what happens when i get more traffic to my script, i think it should slow down server.. Here is how i save IP's:

file with content:
time;IP;
.....
each surfer will add new row to script and delete all IP's that are older than 24h, but trouble is: each surfer open file , read all content and rewrite new file i had idea to settle it by using only some ammount of IP's , but i can't do so, because i need to list these IP's for 24hours...

i'm sure there is any other way how to save all IP's for 24h without reading them'all and rewrite existing file with nwe one. i thought some function that could cut off e.g. last few rows and add new one.. or so...

View 5 Replies View Related

Optimize Pictures On Unix

Feb 21, 2005

I currently have to deal with all the uploading of product pictures myself. I would like to add to my back end PHP program the functions to allow my employees to do it. So I will need to be able to have a program that does several processes that I currently do manually.

I need a program that will run on my unix server that I can call by command line and it will Resize the picture. I need to 2 copies. One that is max 350 x 350 and a thumbnail that is 100 x 100. Keeping tha aspect ratio correct and Optimize the graphic for fast loading. Does anyone know of a program like this?

View 1 Replies View Related

File To Optimize A Database

Dec 6, 2005

is it possible to write a php file that will optimize all tables within a specific database on a regular basis by using a cron job.

View 6 Replies View Related

How To Optimize Query In Zend

Mar 22, 2011

This is my simple query in mysql/zend:

// Get Patients
$table = new Model_Patient_DbTable();
$select = $table->select();

[code]...

View 1 Replies View Related

Phpmyadmin Optimize Table?

Apr 3, 2009

What does "Optimize Table" actually do?

View 3 Replies View Related

Optimize Magento-using Websites?

Mar 17, 2010

If you look at Magento and try to compare with any other non-PHP eCommerce Shopping Cart websites, you will find that the latter are comparatively faster. I know that the following factors work as well:-Hosting Server BandwidthMagento offers lots & lots of features, which others does not (& will not be able to) provide with as much flexible as it offers now.Clients / Administrators can override the core behavior of the Magento functionality, using the custom module functionality, which will be a bit tough for other non-PHP websitesStill for non=PHP websites, programmers can use the concept of stored procedures very easily, with the help of which they can load the whole database in a view & then get disconnected from the database. So whenever any server request for next / previous page occurs, the server just accesses the view to fetch the proper results, instead of going for fetching from the database directly.Also from MySQL 5.0, it has introduced the Stored Procedure concept, but I don't know whether this is at all used in Magento. Even if it is used in Magento, whether will it be of any help for Magento, is another BIG question, because of the whole lot of features Magento provide.

View 1 Replies View Related

Get Rid Of Order By Or Optimize The Query

Apr 19, 2011

I have got query

select field from table order by field asc limit 4000, 30;

Table has got million rows order by on such simple select make big delays. There is index on field. How to get rid of 'order by' and speed up the limit and obtain the same result via select?

My proposition is to change the order of rows and then just make simple select field from table.

View 1 Replies View Related

Simplify The Tables And Optimize?

Apr 12, 2010

Now i have a table with the following fields

id
country
[code].........

View 3 Replies View Related

Optimize Near-duplicate Value Search?

May 20, 2010

I'm trying to find near duplicate values in a set of fields in order to allow an administrator to clean them up.There are two criteria that I am matching onOne string is wholly contained within the other, and is at least 1/4 of its lengthThe strings have an edit distance less than 5% of the total length of the two stringsThe Pseudo-PHP code:

foreach($values as $value){
$matches = array();
foreach($values as $match){
[code]..........

View 2 Replies View Related

Ways To Optimize Html Pages

Oct 19, 2001

I know there are ways to optimize html pages with all of the software out there. But does anyone know if they make one that will crunch PHP without changing the code? Everytime I edit php code in my WYSIWYG editor it leaves spaces that aren't needed in the coding.

View 1 Replies View Related

Find The Bottleneck / Optimize Magento?

Jun 1, 2011

Is there a way to identify quickly a faulty script in Magento that slows down the server?

Magento has something like 35 000 files, so there are so many places it can go wrong, I just need a way to find quickly where to optimize.

For example, could I install a script that would tell me all the files magento reads before outputing a page?

View 2 Replies View Related

SQL Clause To Optimize UPDATE Queries?

Feb 4, 2010

In my fictional database, I have several columns of data. In designing a PHP front-end to the script, it was necessary to allow the user to modify all attributes of a tuple if necessary.If the end-user only ends up modifying one attribute instead of all of them, the following statement:

UPDATE foo
SET name='bar' location='YYZ' drink='ale'
where user='smithj'`
Would update all three attributes of the tuple "smithj", even if two of the attributes are the same.Is there a way to have SQL (MySQL, if it makes a difference) automatically filter out the redundant updates, similar to the IF EXISTS clause in CREATE TABLE IF EXISTS?

View 5 Replies View Related

How To Optimize User Ranking Query

Dec 23, 2010

I have 2 databases (users, userRankings) for a system that needs to have rankings updated every 10 minutes.I use the following code to update these rankings which works fairly well, but there is still a full table scan involved which slows things down with a few hundred thousand users.

mysql_query("TRUNCATE TABLE userRankings");
mysql_query("INSERT INTO userRankings (userid) SELECT id FROM users ORDER BY score DESC");
mysql_query("UPDATE users a, userRankings b SET a.rank = b.rank WHERE a.id = b.userid");

In the userRankings table, rank is the primary key and userid is an index. Both tables are MyISAM (I've wondered if it might be beneficial to make userRankings InnoDB).

View 2 Replies View Related

Optimize And Shorten Union Query?

Dec 5, 2010

Below is my query to get a bulk of attributes of some objects. Are there anyway to optimize and shorten it?

SELECT object_id, value, name FROM attr_text WHERE object_id IN ('43', '42', '41', '40', '39')
UNION
SELECT object_id, value, name FROM attr_varchar WHERE object_id IN ('43', '42', '41', '40', '39')

[code]..

View 2 Replies View Related

Optimize A Query For A Postgresql Database?

Oct 29, 2009

The original code that I'm working on looped through a large set of users, and calculated a date range for each one of them. It would then take that date range and query how many questions they answered and how many they got correct within that date range. Those results were tallied up, and it is those end tallies that we need.What I've already done to speed this up (because it was taking several minutes) is this: Rather than querying each user individually, the script now just loops through each user calculates the date range that would be applicable to them (all other aspects of the query are identical for each user). That data is collected in a 3d array [startDate][endDate][userid], and a single query built to do that operaion on all of the users. Here's an example of the query that gets output:

SELECT COUNT(uapl.id) AS numAnswered,
SUM(CASE WHEN (a.correct OR q.survey OR uapl.answersId IS NULL) THEN 1 ELSE 0 END) AS numCorrect
FROM usersAnswersProgramsLink uapl
[code]...

View 3 Replies View Related

Optimize RegEx For Template Parser

Oct 8, 2010

I need to optimize a RegEx I am using to parse template tags in my CMS. A tag can be either a single tag or a matching pair. An example of some tags:

{static:input:title type="input"}
{static:image:picture}<img src="{$img.src}" width="{$img.width}" height="{$img.height"} />{/static:image:picture}

Here is the RegEx I currently have that properly selects what I need but I ran it through the RegexBuddy debugger and it takes tens of thousands of steps to do one match if the HTML page is quite large.

{static([w:]*)?s?(.*?)}(?!"|')(?:((?:(?!{static1).)*?){/static1})?

When this matches a tag, Group 1 is the parameters which is all the colon separated words. Group 2 is the parameters. And Group 3 (If it's a tag pair) is the content between each tag. I'm also having problems when I stick these tags inside my conditional tags as well. Something like this doesn't match group 2 properly (Group 2 should be blank in both the matched tags below):......

View 2 Replies View Related

Optimize A Non-tail-recursive Function?

Apr 17, 2009

I have this function whose essential operations are outlined as follows:

function render($index) {
foreach($things[$index] as $key => $data) {
[code]....

I can not for the life of me figure out how to optimize this function; I fear that PHP implode if the call stack gets too big.

View 4 Replies View Related

Optimize Error Return On Duplicate Key?

Jul 16, 2010

Just want to get your folks' opinions about optimizing a query, where it's possible the user accidentally tries to input a value for a primary key that's already present. So, just to dumb it down to its complete base, let's say the user was able to pass an ID, which is primary, into an add query (would never happen this way of course; this is just an example).

View 9 Replies View Related

Does Code Optimize Tail Recursion?

May 30, 2011

I wrote a small piece of code that I believe should have succeeded if tail recursion was optimized, however it blew up the stack. Should I conclude PHP does not optimize tail recursion?

function sumrand($n,$sum) {
if ($n== 0) {
return $sum;[code].....

View 2 Replies View Related

Optimize The Speed At Which My Scripts Execute?

Jun 2, 2010

I have zlib and Zend Optimizer enabled on my server.I have read about the

zlib.output_compression

directive. Are there any caveats with turning this directive on in my server?

View 4 Replies View Related

ADVERTISEMENT