Optimize query

[The Little Guy]

Is there a faster way to search my database? I only want a maximum of of 500 rows, but I also want to show how may rows could have been returned. (similar to google).

 

SELECT SQL_CALC_FOUND_ROWS
  title,
  content,
  URL,
  id
FROM $searchType
  WHERE
    MATCH(URL,title,content) AGAINST ('$searchQuery' IN BOOLEAN MODE) AND
content IS NOT NULL
LIMIT $limitvalue, $limit

[cooldude832]

Run the EXPLAIN on it and see if you get any thing fishy in it

 

Indexing title content, url may aid the queries (assuming id is PK)

 

Also I believe if you move the content IS NOT NULL first the query will reduce the total number of rows to query against for the MATCH portion which could help also.  I've been told mysql reads like a book left to right top to bottom in a sense.

 

 

Don't index content if its a large text area

[The Little Guy]

 SELECT SQL_CALC_FOUND_ROWS
title,
content,
URL,
id
FROM $searchType
WHERE 
(`title` LIKE CONVERT( _utf8 '%$searchQuery%' USING latin1 ) COLLATE latin1_swedish_ci)
OR
`content` LIKE CONVERT( _utf8 '%$searchQuery%' USING latin1 ) COLLATE latin1_swedish_ci
OR 
`URL` LIKE CONVERT( _utf8 '%$searchQuery%' USING latin1 ) COLLATE latin1_swedish_ci
LIMIT $limitvalue , $limit 

 

This seems runs quicker.

 

Another question... is there a way I can make something that shows how close a search result matches a search query?

[cooldude832]

Yes and no this is something I like to do more on the php end of things because I can simply say  here is the keywords here is how many keywords that row had and do a division for each match.

 

Google it because what I'd suggest in mysql would be probably slow

 

 

Did you do

 

EXPLAIN

 

SELECT....

 

to see how it looks?

[The Little Guy]

I did what you see above

[cooldude832]

go in phpmyadmin (fill in all php variables with static values)

 

and do

EXPLAIN
SELECT SQL_CALC_FOUND_ROWS
title,
content,
URL,
id
FROM $searchType
WHERE 
(`title` LIKE CONVERT( _utf8 '%$searchQuery%' USING latin1 ) COLLATE latin1_swedish_ci)
OR
`content` LIKE CONVERT( _utf8 '%$searchQuery%' USING latin1 ) COLLATE latin1_swedish_ci
OR 
`URL` LIKE CONVERT( _utf8 '%$searchQuery%' USING latin1 ) COLLATE latin1_swedish_ci
LIMIT $limitvalue , $limit 

 

See what comes up

[The Little Guy]

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	webSearch 	ALL 	URL_2,URL 	NULL 	NULL 	NULL 	16025 	Using where

[cooldude832]

looks okay try adding in indexes on URL_2, URL and Title

[The Little Guy]

it already is...

 

 

INDEX (URL_2): URL, Title

 

FULLTEXT (URL): URL, Title, Content

[cooldude832]

probably the best you are going to get it for now

[fenway]

probably the best you are going to get it for now

Not true.

 

Why aren't you using the FULLTEXT index that you actually have?

[The Little Guy]

because it seems sooo much slower.

[Mchl]

FOUND_ROWS() function will tell you how many results are there in total

[The Little Guy]

FOUND_ROWS() function will tell you how many results are there in total

 

I know, that is what I use in my query.

 

If I remove that, and SQL_CALC_FOUND_ROWS would it speed up the query?

[The Little Guy]

Here is my FULL-TEXT Search:

 

$query_count = sprintf("SELECT SQL_CALC_FOUND_ROWS
  title,
  content,
  URL,
  id
FROM %s
  WHERE
    MATCH(
	`URL`,
	`title`,
	`content`
) AGAINST ('%s' IN BOOLEAN MODE) AND
content IS NOT NULL
LIMIT $limitvalue, $limit",
mysql_real_escape_string($searchType),
mysql_real_escape_string($searchQuery));

$sql = mysql_query($query_count)or die(mysql_error()); 
$result_count = mysql_query("SELECT FOUND_ROWS()")or die(mysql_error());
$total = mysql_fetch_array($result_count);
$totalrows = $total[0];

[Mchl]

 

I know, that is what I use in my query.

 

If I remove that, and SQL_CALC_FOUND_ROWS would it speed up the query?

 

Didn't notice... sorry

 

It probably would speed it up. Although it still is faster, than running a separate query to get number of rows.

[fenway]

because it seems sooo much slower.

Does the EXPLAIN confirm this?

[The Little Guy]

EXPLAIN doesn't say how long the query took to return the results.

 

So... what do you mean by that?

[Mchl]

EXPLAIN doesn't say how long the query took to return the results.

 

Well... mysql console does...

And MySQL Query Browser... and some other tools I suppose