faster queries

[The Little Guy]

OK, how could I improve performance?

 

My first query gets all the results, so I know how many pages to have

 

My second query is to get the results that will be shown on the page.

 

<?php
// Query to get ALL valid results
$query_count = "SELECT title,content,URL, 
MATCH(URL) AGAINST ('$searchQuery' IN BOOLEAN MODE) AS score1,
MATCH(title) AGAINST ('$searchQuery' IN BOOLEAN MODE) AS score2,
MATCH(content) AGAINST ('$searchQuery' IN BOOLEAN MODE) AS score3 
FROM $searchType WHERE MATCH(title) AGAINST ('$searchQuery' IN BOOLEAN MODE) OR
MATCH(URL) AGAINST ('$searchQuery' IN BOOLEAN MODE) OR
MATCH(content) AGAINST ('$searchQuery' IN BOOLEAN MODE)";
$result_count = mysqli_query($db,$query_count);    
$row = mysqli_fetch_array($result_count);  
$totalrows = mysqli_num_rows($result_count);

$limitvalue = $page * $limit - ($limit);  

$time_start = microtime(true);
// Same query as above, only it returns a limited number of results ($limit = 5 to 100)
$query = "SELECT title,content,URL,id,
MATCH(URL) AGAINST ('$searchQuery' IN BOOLEAN MODE) AS score1,
MATCH(title) AGAINST ('$searchQuery' IN BOOLEAN MODE) AS score2,
MATCH(content) AGAINST ('$searchQuery' IN BOOLEAN MODE) AS score3 
FROM $searchType WHERE MATCH(title) AGAINST ('$searchQuery' IN BOOLEAN MODE) OR
MATCH(URL) AGAINST ('$searchQuery' IN BOOLEAN MODE) OR
MATCH(content) AGAINST ('$searchQuery' IN BOOLEAN MODE)
ORDER BY score1 DESC, score2 DESC, score3 DESC LIMIT $limitvalue, $limit";

?>

 

I don't know if it is just me, but It seems like this would take a while to query, if it needed to query 100,000,000+ rows in a database.

 

What would the fastest way to do this be?

[BlueSkyIS]

hm, if you're going to be consistently querying MATCHING AGAINST 100,000,000+ records you're probably going to need a multi-sever solution. at very least you're going to want to hold your data in RAM and not hit the hard drive for each query.

 

otherwise, i think you're doing what's necessary to determine the total number of records and then select a subset.

[roopurt18]

At the very least change your first query to:

 

SELECT COUNT(*) AS `Num` FROM ...

That way you aren't needlessly shuffling all that data around just to determine a record count.

[The Little Guy]

then I get a total of 1

[roopurt18]

  $q = mysql_query("SELECT COUNT(*) AS `Num` FROM ...");
  $count = $q && ($row = mysql_fetch_assoc($q)) ? $row['Num'] : 0;
  echo $count;

[The Little Guy]

then where do I place my MATCH AGAINST?

 

Your way you show will/may not return the same results, so the page count will/may be off.

[roopurt18]

then where do I place my MATCH AGAINST?

 

In the WHERE clause.

 

[Psycho]

If you are concerned about the time to do the query twice, then just do the query once to get the data. Then get the count of records from num_rows. You can get the rows of data you need by manually moving the pointer.

[The Little Guy]

<?php
$query_count = "SELECT SQL_CALC_FOUND_ROWS
  title,
  content,
  URL,
  id,
    (MATCH(URL) 
    AGAINST ('%$searchQuery%' IN BOOLEAN MODE) * 1.5) +
  (MATCH(title) 
    AGAINST ('%$searchQuery%' IN BOOLEAN MODE) * 0. +
  (MATCH(content) 
    AGAINST ('%$searchQuery%' IN BOOLEAN MODE) * 0.5) AS score
FROM $searchType
  WHERE
    MATCH(title, URL, content)
      AGAINST ('%$searchQuery%' IN BOOLEAN MODE)
AND content != ''
ORDER
  BY score DESC LIMIT $limitvalue, $limit";  

$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];
?>

 

OK... I am down to one SQL statement, but... it still seems to run slow on 4,420 recods... what should I do?

 

If I do a search, something with the most results (free), it takes 1.619 Seconds to do just the query.

 

I was thinking, would it be faster to do a search on 3 tables instead of one?

 

- One with the URL's

- One with the titles

- One with the content